database upgrade from 1.8.10 to 2.0 fails

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

    database upgrade from 1.8.10 to 2.0 fails

    Hi,

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

    [email protected]:/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:
    [email protected]:/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.
    Last edited by verus; 23-05-2012, 12:21.

    #2
    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 '='

    Comment


      #3
      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...
      Last edited by verus; 21-11-2012, 12:56.

      Comment


        #4
        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

        Comment


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

          [email protected]:/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)
          [email protected]:/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)
          [email protected]:/data/software/zabbix-2.0.0/upgrades/dbpatches/2.0/mysql#

          Comment


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



            [email protected]:~/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 ^^
            Last edited by sheen; 23-05-2012, 15:02.

            Comment


              #7
              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.

              Comment


                #8
                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.

                Comment


                  #9
                  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)..

                  Comment


                    #10
                    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.

                    Comment


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

                      [[email protected] 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

                      Comment


                        #12
                        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

                        Comment


                          #13
                          Originally posted by dougbee View Post
                          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.

                          Comment


                            #14
                            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)
                            Last edited by dougbee; 24-05-2012, 17:44.

                            Comment


                              #15
                              Originally posted by verus View Post

                              [email protected]:/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?

                              Comment

                              Announcement

                              Collapse
                              No announcement yet.
                              Working...
                              X