Navigation

    Kopano
    • Register
    • Login
    • Search
    • Categories
    • Get Official Kopano Support
    • Recent
    Statement regarding the closure of the Kopano community forum and the end of the community edition

    Kopano core Update MySQL duplicated Entry

    Kopano Groupware Core
    9
    18
    2490
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • klipp
      klipp last edited by

      same problem here

      1 Reply Last reply Reply Quote 0
      • tommi
        tommi @Joost last edited by

        Same here
        @joost

        Sun Apr 15 16:07:18 2018: [=======] Starting kopano-server version 8.6.80 (pid 3094)
Sun Apr 15 16:07:20 2018: [error  ] SQL [00000007] Failed: Duplicate entry 'Content-Class' for key 'gns', Query Size: 170, Query: "ALTER TAB
Sun Apr 15 16:07:20 2018: [error  ] KDatabase::I_Update() query failed: "Duplicate entry 'Content-Class' for key 'gns'", query: ALTER TABLE
Sun Apr 15 16:07:20 2018: [error  ] K-1216: Cannot update to schema v69 because the "names" table contains unexpected rows.
Sun Apr 15 16:07:20 2018: [error  ] SQL [00000007] Failed: SELECT command denied to user 'kopano'@'localhost' for table 'information_schema'
Sun Apr 15 16:07:20 2018: [error  ] KDatabase::DoSelect(): query failed: "SELECT command denied to user 'kopano'@'localhost' for table 'info
Sun Apr 15 16:07:20 2018: [error  ] K-1217: Table fill level is indeterminate.
Sun Apr 15 16:07:20 2018: [error  ] K-1219: K-1216 may have already caused a loss of data in other tables.
Sun Apr 15 16:07:20 2018: [error  ] K-1221: Proceeding with --ignore-da and ignoring the schema update is technically possible, but data cor
Sun Apr 15 16:07:20 2018: [error  ] Can't update the database: Unable to upgrade database from version 8.6.80.0.68 to 8.6.80.0.70
Sun Apr 15 16:07:20 2018: [=======] Server shutdown complete.

        Is there a solution to fix it.

        1 Reply Last reply Reply Quote 0
        • Gerald
          Gerald last edited by

          Got the same problem after updating from core-8.6.80.353_0+29-Ubuntu_16.04 ==> core-8.6.80.590_0+59-Ubuntu_16.04

          Mon Apr 16 11:04:17 2018: [=======] Starting kopano-server version 8.6.80 (pid 16209)
Mon Apr 16 11:04:17 2018: [error  ] Coredumps will not be generated: kopano-server requires the fs.suid_dumpable sysctl to contain the value 2, not 0. See kopano-coredump(5) for details.
Mon Apr 16 11:04:17 2018: [error  ] SQL [00003101] Failed: Duplicate entry '35352' for key 'gni', Query Size: 170, Query: "ALTER TABLE `names` ADD UNIQUE INDEX `gni` (`guid`(16), `nameid`), ADD UNIQUE INDEX `gns` (`guid`(1`), DROP INDEX `guidnameid`, DROP INDEX `guidnamestring`"
Mon Apr 16 11:04:17 2018: [error  ] KDatabase::I_Update() query failed: "Duplicate entry '35352' for key 'gni'", query: ALTER TABLE `names` ADD UNIQUE INDEX `gni` (`guid`(16), `nameid`), ADD UNIQUE INDEX `gns` (`guid`(16), DROP INDEX `guidnameid`, DROP INDEX `guidnamestring`
Mon Apr 16 11:04:17 2018: [error  ] K-1216: Cannot update to schema v69, because the "names" table contains unexpected rows. Certain prior versions of the server erroneously allowed these duplicates to be added (KC-1108).
Mon Apr 16 11:04:17 2018: [error  ] K-1220: To fix the excess rows, use `kopano-dbadm k-1216`. Consult the manpage and preferably make a backup first.
Mon Apr 16 11:04:17 2018: [error  ] K-1221: Alternatively, the server may be started with --ignore-da to forego the schema update.
Mon Apr 16 11:04:17 2018: [error  ] Can't update the database: Unable to upgrade database from version 8.6.80.0.68 to 8.6.80.0.70

          So should I run “kopano-dbadm k-1216” ?

          fbartels 1 Reply Last reply Reply Quote 0
          • fbartels
            fbartels Kopano @Gerald last edited by

            Hi @Gerald ,

            @gerald said in Kopano core Update MySQL duplicated Entry:

            Mon Apr 16 11:04:17 2018: [error ] K-1220: To fix the excess rows, use kopano-dbadm k-1216. Consult the manpage and preferably make a backup first.
            So should I run “kopano-dbadm k-1216” ?

            yes, this is the improvement we were working in in regards to this error message. To get your server to start again, you have to run the given command (and don’t forget to make a backup first ;-) ).

            For statistics purposes it would be very interesting to us how large your database is, how fast your cpu is and how long the command took to run (there is an example from a testing environment already in the man page).

            Regards Felix

            Resources:
            https://kopano.com/blog/how-to-get-kopano/
            https://documentation.kopano.io/
            https://kb.kopano.io/

            Support overview:
            https://kopano.com/support/

            1 Reply Last reply Reply Quote 0
            • Gerald
              Gerald last edited by

              Database size: My database backup file is 2.5GB uncompressed, 266MB when bzip2-compressed.
              CPU: Intel Celeron J3455 (Quad Core “Goldmont / Apollo Lake” 1.5GHz Base, 2.3GHz Boost - quite modern, but Atom based CRAP - so not very fast in single-threaded applications)
              RAM: 8GB, Hard Disk is a SATA SSD.

              Even though the CPU is crap, kopano-dbadm k-1216 took only a bit over a minute I think.
              kopano-server is now working again, first impression looks fine. Thanks for the quick response!

              1 Reply Last reply Reply Quote 1
              • Gerald
                Gerald last edited by Gerald

                I’ve now also tried this on a different system with a bigger database (probably 10x the database size, also a database that has been migrated from older Zarafa versions) and it did not work as flawlessly as on my very small Kopano system.

                Had lots of problems:

                dup: merging 8639 into 17 (properties)...
[error  ] 0xed63c8: SQL Failed: Duplicate entry '10456162-34066-3' for key 'PRIMARY', Query: "UPDATE properties SET tag=34066 WHERE tag=42688"
[error  ] KDatabase::I_Update() query failed: "Duplicate entry '10456162-34066-3' for key 'PRIMARY'", query: UPDATE properties SET tag=34066 WHERE tag=42688

                defrag: moving 3528 -> 3527 (names) (properties) (tproperties)[error  ] 0x24a23c8: SQL Failed: Duplicate entry '118-37576-4430511-30' for key 'PRIMARY', Query: "UPDATE tpro                                                     perties SET tag=37576 WHERE tag=37577"
[error  ] KDatabase::I_Update() query failed: "Duplicate entry '118-37576-4430511-30' for key 'PRIMARY'", query: UPDATE tproperties SET tag=37576 WHERE tag=37577

                defrag: moving 3528 -> 3527 (names) (properties) (tproperties)
[error  ] 0x24a23c8: SQL Failed: Duplicate entry '118-37576-4430511-30' for key 'PRIMARY', Query: "UPDATE tpro                                                     perties SET tag=37576 WHERE tag=37577"
[error  ] KDatabase::I_Update() query failed: "Duplicate entry '118-37576-4430511-30' for key 'PRIMARY'", query: UPDATE tproperties SET tag=37576 WHERE tag=37577
[error  ] dbadm: action failed: disk error (80000007)

                [error  ] 0x8d83c8: SQL Failed: Duplicate entry '10456162-34066-3' for key 'PRIMARY', Query: "UPDATE properties SET tag=34066 WHERE tag=42732"
[error  ] KDatabase::I_Update() query failed: "Duplicate entry '10456162-34066-3' for key 'PRIMARY'", query: UPDATE properties SET tag=34066 WHERE tag=42732

                defrag: moving 3529 -> 3528 (names) (properties) (tproperties)[error  ] 0x23083c8: SQL Failed: Duplicate entry '115-37577-9639872-30' for key 'PRIMARY', Query: "UPDATE tproperties SET tag=37577 WHERE tag=37578"
[error  ] KDatabase::I_Update() query failed: "Duplicate entry '115-37577-9639872-30' for key 'PRIMARY'", query: UPDATE tproperties SET tag=37577 WHERE tag=37578
[error  ] dbadm: action failed: disk error (80000007)

                I got from one error to the next mostly by opening database in phpmyadmin, searching for the duplicate entry mentioned in the error and then manually deleting those not-much-information-containing high IDs, below is an example screenshot from phpmyadmin.
                I never was able to resolve all errors since due to the large database every single operation in phpmyadmin like searching or viewing a search result took several minutes each and I was not able to make much progress therefore… But I finally got the new version of Kopano to start by running the other options of kopano-dbadm individually and fixing a couple of things manually in phpmyadmin.

                kopano-dbadm np-remove-highid
kopano-dbadm np-repair-dups
kopano-dbadm np-remove-xh
kopano-dbadm np-defrag

                And while I never was able to eliminate all abortion errors in the defrag run, the others worked and the new Version of Kopano was now able to start… So I’m pretty happy for now. Just documenting that I ran into trouble for other people - I’m pretty sure I won’t be the only one person where “kopano-dbadm k-1216” will abort.

                0_1524138028163_IMG_0364.PNG

                1 Reply Last reply Reply Quote 0
                • lYSator
                  lYSator last edited by

                  After Update to 8.6.80.621 and runnig “kopano-dbadm k-1216” my problems are solfed!

                  Thank you!

                  1 Reply Last reply Reply Quote 0
                  • Coffee_is_life
                    Coffee_is_life last edited by

                    @lYSator,

                    how big is your database and would you share your system config? (CPU, RAM etc)

                    coffee_is_life

                    1 Reply Last reply Reply Quote 0
                    • lYSator
                      lYSator last edited by

                      i got following size with this sql statement:

                      SELECT table_schema "Name", sum( data_length + index_length ) / 1024 / 1024 "database size in MB", sum( data_free )/ 1024 / 1024 "free space in MB" FROM information_schema.TABLES WHERE table_schema = 'zarafa' GROUP BY table_schema

                      Name database size in MB free space in MB
                      zarafa 691.28125000 1825.00000000

                      My Server is an VPS with following specs:
                      processor : 0
                      vendor_id : GenuineIntel
                      cpu family : 6
                      model : 60
                      model name : Intel® Core™ i7-4785T CPU @ 2.20GHz
                      cpu MHz : 1900.000
                      cache size : 8192 KB

                      processor : 1
                      vendor_id : GenuineIntel
                      cpu family : 6
                      model : 60
                      model name : Intel® Core™ i7-4785T CPU @ 2.20GHz
                      cpu MHz : 1900.000
                      cache size : 8192 KB

                      processor : 2
                      vendor_id : GenuineIntel
                      cpu family : 6
                      model : 60
                      model name : Intel® Core™ i7-4785T CPU @ 2.20GHz
                      cpu MHz : 1900.000
                      cache size : 8192 KB

                      processor : 3
                      vendor_id : GenuineIntel
                      cpu family : 6
                      model : 60
                      model name : Intel® Core™ i7-4785T CPU @ 2.20GHz
                      cpu MHz : 1900.000
                      cache size : 8192 KB

                      Software
                      Betriebssystem: Debian 8 64Bit minimal
                      Produkt
                      Paket: VPS Pro Hybrid Edition
                      CPU: 4 Kern(e)
                      RAM: 2048 MB
                      SWAP: 8192 MB
                      DISK: 240 GB
                      Datenvolumen: 1024 GB / Monat
                      Netzwerk-Geschwindigkeit: 1000 Mbit/s

                      Datenbank-Server
                      Server: Localhost via UNIX socket
                      Server-Typ: MySQL
                      Server Version: 5.5.59-0+deb8u1 - (Debian)
                      Protokoll-Version: 10

                      1 Reply Last reply Reply Quote 0
                      • Jo-TL
                        Jo-TL last edited by 

                        [error  ] SQL [00000004] Failed: Duplicate entry 'x-no-relay' for key 'gns', Query Size: 170, Query: "ALTER TABLE `names` ADD UNIQUE INDEX `gni` (`guid`(16), `nameid`), ADD UNIQUE INDEX `gns` (`guid`(16), `namestring`), DROP INDEX `guidnameid`, DROP INDEX `guidnamestring`"
[error  ] KDatabase::I_Update() query failed: "Duplicate entry 'x-no-relay' for key 'gns'", query: ALTER TABLE `names` ADD UNIQUE INDEX `gni` (`guid`(16), `nameid`), ADD UNIQUE INDEX `gns` (`guid`(16), `namestring`), DROP INDEX `guidnameid`, DROP INDEX `guidnamestring`
[error  ] K-1216: Cannot update to schema v69, because the "names" table contains unexpected rows. Certain prior versions of the server erroneously allowed these duplicates to be added (KC-1108).
[error  ] K-1220: To fix the excess rows, use `kopano-dbadm k-1216`. Consult the manpage and preferably make a backup first.
[error  ] K-1221: Alternatively, the server may be started with --ignore-da to forego the schema update.
[warning] WARNING: Unable to upgrade database from version 8.5.9.2153316352.68 to 8.5.9.2153316352.70
[warning]    You can force the server to start with --ignore-database-version-conflict
[warning]    Warning, you can lose data! If you don't know what you're doing, you shouldn't be using this option!

                        After the recent update the server stays offline.

                        The “kopano-dbadm k-1216” command seams to fix the problem for me.

                        1 Reply Last reply Reply Quote 0
                        • First post
                          Last post