database upgrade from 1.8.10 to 2.0 fails

[verus]

Hi,

I want to upgrade from 1.8.10 to 2.0 and when I run the upgrade script for the database I get:

root@blnxvs02:/data/software/zabbix-2.0.0/upgrades/dbpatches/2.0/mysql# ./upgrade -p zabbix
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
Patching the database
Enter password:
ERROR 1452 (23000) at line 104: Cannot add or update a child row: a foreign key constraint fails (temp file operation failed)
Failed to patch Zabbix database. Restore from backup

Then I tried to patch it manually and I get:
root@blnxvs02:/data/software/zabbix-2.0.0/upgrades/dbpatches/2.0/mysql# mysql -u root -p zabbix < ./patch.sql
Enter password:
ERROR 1005 (HY000) at line 6: Can't create table 'zabbix.#sql-53a_2e5' (errno: 121)

I get the same result when I rerun the upgrade script.

What am I doing wrong ?
thanks, Verus.

[rrr]

on my system database upgrade fails too, but with other error-message:

ERROR 1267 (HY000) at line 860: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

[verus]

I tried again and I don't know how but now the database upgrade didn't complain and I could resume the installation.
After the installation (without any error) I get the following:

Printscreen attached...

So something isn't right...

[rastaferraille]

Hello,

It doesn't work for me from 1.8.4 too. Patch.sql generates this error :
ERROR 1175 (HY000) at line 4: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

[verus]

I restored everything and upgraded first to 1.8.13 (you never know...) without a problem.
Then again to 2.0, the problem stays:

root@blnxvs02:/data/software/zabbix-2.0.0/upgrades/dbpatches/2.0/mysql# mysql -uroot -p zabbix < ./patch.sql
Enter password:
ERROR 1452 (23000) at line 104: Cannot add or update a child row: a foreign key constraint fails (temp file operation failed)
root@blnxvs02:/data/software/zabbix-2.0.0/upgrades/dbpatches/2.0/mysql# mysql -uroot -p zabbix < ./patch.sql
Enter password:
ERROR 1005 (HY000) at line 6: Can't create table 'zabbix.#sql-575_22c' (errno: 121)
root@blnxvs02:/data/software/zabbix-2.0.0/upgrades/dbpatches/2.0/mysql#

[sheen]

Hi, with MySQL database upgrade, I got an issue from 1.8.12 to 2.0 :



root@Zabbix:~/zabbix-2.0.0/upgrades/dbpatches/2.0/mysql# mysql -D zabbix -u root -p < patch.sql

ERROR 1452 (23000) at line 104: Cannot add or update a child row: a foreign key constraint fails (`zabbix/#sql-9c9_29052`, CONSTRAINT `c_config_2` FOREIGN KEY (`discovery_groupid`) REFERENCES `groups` (`groupid`))


I've restored the database, and wait for a fix ^^

[verus]

seems like this upgrade isn't as stable as it should.
I never had problems with db upgrades before with zabbix so I guess the problem is with this version...

Hope someone can help me out.
thanks.

[verus]

Hi,

I retried and now used the command:

./upgrade zabbix --force

The only difference is the --force command.
I received a lot of errors like this:
ERROR 1054 (42S22) at line 215: Unknown column 'noneworktimecolor' in 'graph_theme'
ERROR 1060 (42S21) at line 383: Duplicate column name 'ns'
ERROR 1060 (42S21) at line 387: Duplicate column name 'ns'
ERROR 1060 (42S21) at line 390: Duplicate column name 'ns'
ERROR 1060 (42S21) at line 393: Duplicate column name 'ns'
ERROR 1060 (42S21) at line 397: Duplicate column name 'ns'
ERROR 1060 (42S21) at line 401: Duplicate column name 'ns'
ERROR 1060 (42S21) at line 405: Duplicate column name 'ns'
ERROR 1060 (42S21) at line 408: Duplicate column name 'ns'
ERROR 1146 (42S02) at line 412: Table 'zabbix.hosts_profiles' doesn't exist
ERROR 1146 (42S02) at line 413: Table 'zabbix.hosts_profiles_ext' doesn't exist
ERROR 1050 (42S01) at line 415: Table 'host_inventory' already exists
ERROR 1005 (HY000) at line 490: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1146 (42S02) at line 570: Table 'zabbix.hosts_profiles' doesn't exist
ERROR 1051 (42S02) at line 679: Unknown table 'hosts_profiles'
ERROR 1051 (42S02) at line 680: Unknown table 'hosts_profiles_ext'
ERROR 1025 (HY000) at line 685: Error on rename of './zabbix/#sql-2afe_267' to './zabbix/hostmacro' (errno: 150)
ERROR 1061 (42000) at line 687: Duplicate key name 'hostmacro_1'
ERROR 1005 (HY000) at line 688: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1025 (HY000) at line 692: Error on rename of './zabbix/#sql-2afe_267' to './zabbix/hosts_groups' (errno: 150)
ERROR 1061 (42000) at line 695: Duplicate key name 'hosts_groups_1'
ERROR 1005 (HY000) at line 696: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1005 (HY000) at line 697: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1050 (42S01) at line 702: Table 'interface' already exists
ERROR 1061 (42000) at line 713: Duplicate key name 'interface_1'
ERROR 1061 (42000) at line 714: Duplicate key name 'interface_2'
ERROR 1005 (HY000) at line 715: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1054 (42S22) at line 718: Unknown column 'ip' in 'field list'
ERROR 1054 (42S22) at line 725: Unknown column 'ip' in 'field list'
ERROR 1054 (42S22) at line 732: Unknown column 'ip' in 'field list'
ERROR 1054 (42S22) at line 740: Unknown column 'ipmi_ip' in 'field list'
ERROR 1060 (42S21) at line 748: Duplicate column name 'name'
ERROR 1005 (HY000) at line 777: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1005 (HY000) at line 778: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1005 (HY000) at line 779: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1005 (HY000) at line 780: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1054 (42S22) at line 782: Unknown column 'snmp_port' in 'field list'
ERROR 1091 (42000) at line 783: Can't DROP 'snmp_port'; check that column/key exists
ERROR 1061 (42000) at line 785: Duplicate key name 'items_5'
ERROR 1062 (23000) at line 881: Duplicate entry '11899-web.test.error[www.google.be]' for key 'items_1'
ERROR 1062 (23000) at line 887: Duplicate entry '2-37464' for key 'httptestitem_httptestitem_1'
ERROR 1062 (23000) at line 894: Duplicate entry '3-37464' for key 'items_applications_1'
ERROR 1091 (42000) at line 904: Can't DROP 'ip'; check that column/key exists
ERROR 1061 (42000) at line 936: Duplicate key name 'hosts_4'
ERROR 1005 (HY000) at line 937: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1005 (HY000) at line 938: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1005 (HY000) at line 973: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1005 (HY000) at line 974: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1005 (HY000) at line 980: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1005 (HY000) at line 986: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1005 (HY000) at line 987: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1091 (42000) at line 988: Can't DROP 'lastcheck'; check that column/key exists
ERROR 1005 (HY000) at line 999: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)
ERROR 1050 (42S01) at line 1001: Table 'icon_map' already exists
ERROR 1061 (42000) at line 1007: Duplicate key name 'icon_map_1'
ERROR 1005 (HY000) at line 1008: Can't create table 'zabbix.#sql-2afe_267' (errno: 121)


But after that I completed the upgrade and now I can use zabbix 2.0.
I have my hosts, items, triggers, actions and it seems to work.

[dougbee]

I'm getting this error:

ERROR 1452 (23000) at line 1717: Cannot add or update a child row: a foreign key constraint fails (`zabbix`.<result 2 when explaining filename '#sql-cda_1cf15'>, CONSTRAINT `c_sysmaps_1` FOREIGN KEY (`backgroundid`) REFERENCES `images` (`imageid`))

This is 1.8.12 with with MySQL 5.1.49-3 (Debian 6.0)..

[verus]

try the "./upgrade zabbix --force" command first without --force, after that with --force.

Does it make a difference ?
If not, I don't know, I'm not a database expert.

[RaymondH]

I'm getting this error when I try to update on one of our test instances.

[root@localhost mysql]# ./upgrade -uzabbix -p -Dzabbix
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
Patching the database
ERROR 1005 (HY000) at line 6: Can't create table './zabbix/#sql-4896_5.frm' (errno: 121)
Failed to patch Zabbix database. Restore from backup

[softy]

Did you stopped that script first time?
This error i got, when i stopped the script, because it looks like it won't work.
I could only solve this with the import of my database backup.

It does something; for a VERY long time for my small test database.
Don't know if this will work,because my table space was full

[dougbee]

I'm getting this error:

ERROR 1452 (23000) at line 1717: Cannot add or update a child row: a foreign key constraint fails (`zabbix`.<result 2 when explaining filename '#sql-cda_1cf15'>, CONSTRAINT `c_sysmaps_1` FOREIGN KEY (`backgroundid`) REFERENCES `images` (`imageid`))

This is 1.8.12 with with MySQL 5.1.49-3 (Debian 6.0)..

A bit more detail. An apt-get upgrade installed MySQL 5.1.61-0+squeeze1, but I get the same error. I did a "show engine innodb status" for more info, and get this:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
120524 10:32:57 Transaction:
TRANSACTION 0 63867446, ACTIVE 0 sec, process no 23152, OS thread id 140498707810048 inserting, thread declared inside InnoDB 495
mysql tables in use 2, locked 2
6 lock struct(s), heap size 1216, 5 row lock(s), undo log entries 3
MySQL thread id 44, query id 3466 zabbix copy to tmp table
ALTER TABLE sysmaps ADD CONSTRAINT c_sysmaps_1 FOREIGN KEY (backgroundid) REFERENCES images (imageid)
Foreign key constraint fails for table `zabbix`.<result 2 when explaining filename '#sql-5a70_2c'>:
,
CONSTRAINT `c_sysmaps_1` FOREIGN KEY (`backgroundid`) REFERENCES `images` (`imageid`)
Trying to add in child table, in index `c_sysmaps_1` tuple:
DATA TUPLE: 2 fields;
0: len 8; hex 0000000000000015; asc ;; 1: len 8; hex 0000000000000004; asc ;;

But in parent table `zabbix`.`images`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 8; hex 0000000000000018; asc ;; 1: len 6; hex 000003ce80ee; asc ;; 2: len 7; hex 800000002d0264; asc - d;; 3: len 4; hex 80000001; asc ;; 4: len 13; hex 4c69676874202d20477265656e; asc Light - Green;; 5: len 30; hex 89504e470d0a1a0a0000000d49484452000000640000006408 0600000070; asc PNG IHDR d d p;...(truncated);

Let me know if there's more info I can provide.

[dougbee]

Since it seemed to be tripping up on the images (and I have a few custom images and backgrounds) I tried deleting them from Zabbix.

In the end I deleted all images and backgrounds (this is my test instance, which I'm getting pretty good at restoring) but the patch.sql still fails on the same line.

BUT - deleting all the maps (and leaving all the images intact) - allows the patch to run. I'll do one more test and see if I can export the maps, delete the maps, do the patch, and re-import.

Update:

Deleting and re-importing the maps (once MySQL had been patched and zabbix upgraded) worked. As this is a test environment I only had one non-empty map with a single icon, which now seems out of alignment versus its original. (the icon has moved to the top/left origin yet its label remains in the original position)

[CeeEss]

root@blnxvs02:/data/software/zabbix-2.0.0/upgrades/dbpatches/2.0/mysql# ./upgrade -p zabbix
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.
.

Ok ... a database upgrade script created by Zabbix to upgrade a database of their design is UNSUPPORTED? C'MON! What's that all about?