items-output.txt
patch.sql
[ZBX-6831] upgrade zabbix from 1.8.8 to 2.0.6 failed with mysql errors Created: 2013 Jul 29 Updated: 2017 May 30 Resolved: 2013 Jul 31 |
|
| Status: | Closed |
| Project: | ZABBIX BUGS AND ISSUES |
| Component/s: | None |
| Affects Version/s: | None |
| Fix Version/s: | 2.0.8rc1, 2.1.2 |
| Type: | Incident report | Priority: | Critical |
| Reporter: | sugun | Assignee: | Unassigned |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | database | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Attachments: |
items-output.txt
patch.sql
|
| Description |
|
Hi, I was trying to upgrade zabbix from 1.8.8 to 2.0.6. File name : /opt/zabbix-2.0.6/upgrades/dbpatches/2.0/mysql/patch.sql I applied the mysql patch(patch.sql) to the existing mysql db and it failed with below error: ERROR 1364 (HY000) at line 907: Field 'params' doesn't have a default value I attached the same file with this request. Could some one help me out? Thanks |
| Comments |
| Comment by Andrei Gushchin (Inactive) [ 2013 Jul 29 ] |
|
show please output of show create table items\G; |
| Comment by sugun [ 2013 Jul 29 ] |
|
Here is the output:
mysql> show create table items\G;
*************************** 1. row ***************************
Table: items
Create Table: CREATE TABLE `items` (
`itemid` bigint(20) unsigned NOT NULL DEFAULT '0',
`type` int(11) NOT NULL DEFAULT '0',
`snmp_community` varchar(64) NOT NULL DEFAULT '',
`snmp_oid` varchar(255) NOT NULL DEFAULT '',
`snmp_port` int(11) NOT NULL DEFAULT '161',
`hostid` bigint(20) unsigned NOT NULL DEFAULT '0',
`description` varchar(255) NOT NULL DEFAULT '',
`key_` varchar(255) NOT NULL DEFAULT '',
`delay` int(11) NOT NULL DEFAULT '0',
`history` int(11) NOT NULL DEFAULT '90',
`trends` int(11) NOT NULL DEFAULT '365',
`lastvalue` varchar(255) DEFAULT NULL,
`lastclock` int(11) DEFAULT NULL,
`prevvalue` varchar(255) DEFAULT NULL,
`status` int(11) NOT NULL DEFAULT '0',
`value_type` int(11) NOT NULL DEFAULT '0',
`trapper_hosts` varchar(255) NOT NULL DEFAULT '',
`units` varchar(10) NOT NULL DEFAULT '',
`multiplier` int(11) NOT NULL DEFAULT '0',
`delta` int(11) NOT NULL DEFAULT '0',
`prevorgvalue` varchar(255) DEFAULT NULL,
`snmpv3_securityname` varchar(64) NOT NULL DEFAULT '',
`snmpv3_securitylevel` int(11) NOT NULL DEFAULT '0',
`snmpv3_authpassphrase` varchar(64) NOT NULL DEFAULT '',
`snmpv3_privpassphrase` varchar(64) NOT NULL DEFAULT '',
`formula` varchar(255) NOT NULL DEFAULT '1',
`error` varchar(128) NOT NULL DEFAULT '',
`lastlogsize` int(11) NOT NULL DEFAULT '0',
`logtimefmt` varchar(64) NOT NULL DEFAULT '',
`templateid` bigint(20) unsigned NOT NULL DEFAULT '0',
`valuemapid` bigint(20) unsigned NOT NULL DEFAULT '0',
`delay_flex` varchar(255) NOT NULL DEFAULT '',
`params` text NOT NULL,
`ipmi_sensor` varchar(128) NOT NULL DEFAULT '',
`data_type` int(11) NOT NULL DEFAULT '0',
`authtype` int(11) NOT NULL DEFAULT '0',
`username` varchar(64) NOT NULL DEFAULT '',
`password` varchar(64) NOT NULL DEFAULT '',
`publickey` varchar(64) NOT NULL DEFAULT '',
`privatekey` varchar(64) NOT NULL DEFAULT '',
`mtime` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`itemid`),
UNIQUE KEY `items_1` (`hostid`,`key_`),
KEY `items_3` (`status`),
KEY `items_4` (`templateid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
| Comment by sugun [ 2013 Jul 29 ] |
|
Attaching the output of "show create table items\G"; |
| Comment by richlv [ 2013 Jul 29 ] |
|
looks like your database schema is broken somehow, 1.8 schema should have a default value of '' (empty) for that field - that is true for 1.8 branch svn head and also for the 1.8.8 release |
| Comment by sugun [ 2013 Jul 30 ] |
|
Richlv, Thanks |
| Comment by sugun [ 2013 Jul 30 ] |
|
Richlv, Could you please explain if I am looking at wrong location or I misunderstood your statement? Thanks |
| Comment by sugun [ 2013 Jul 30 ] |
|
Here are the different ways the schema has been defined for various databases: So from this its clearly evident that for mysql db, the params column should not be empty. Please clarify and suggest a fix to overcome my upgrade issue. Thanks |
| Comment by Andris Zeila [ 2013 Jul 31 ] |
|
Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-6831 |
| Comment by Alexander Vladishev [ 2013 Jul 31 ] |
|
Successfully tested! |
| Comment by Andris Zeila [ 2013 Aug 01 ] |
|
Released in: |