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
    2703
    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.
    • lYSator
      lYSator last edited by

      Hi,

      when i try to Update kopano-core to Version core-8.6.80.502_0+52-Debian_8.0-amd64 i get following error in kopano/server.log

      Mon Apr  9 10:24:17 2018: [=======] Starting kopano-server version 8.6.80 (pid 17780)
Mon Apr  9 10:24:17 2018: [warning] Config warning: Option 'server_max_keep_alive_requests' is not used anymore.
Mon Apr  9 10:24: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  9 10:24:19 2018: [error  ] SQL [00026842] Failed: Duplicate entry '\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' 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`"
Mon Apr  9 10:24:19 2018: [error  ] KDatabase::I_Update() query failed: "Duplicate entry '\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' 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`
Mon Apr  9 10:24:19 2018: [error  ] K-1216: Cannot update to schema v69 because the "names" table contains unexpected rows.
Mon Apr  9 10:24:19 2018: [error  ] SQL [00026842] Failed: SELECT command denied to user 'zarafa'@'localhost' for table 'information_schema', Query Size: 104, Query: "SELECT `AUTO_INCREMENT` FROM mysql.information_schema WHERE table_schema="zarafa" AND table_name="names""
Mon Apr  9 10:24:19 2018: [error  ] KDatabase::DoSelect(): query failed: "SELECT command denied to user 'zarafa'@'localhost' for table 'information_schema'", query: SELECT `AUTO_INCREMENT` FROM mysql.information_schema WHERE table_schema="zarafa" AND table_name="names"
Mon Apr  9 10:24:19 2018: [error  ] K-1217: Table fill level is indeterminate.
Mon Apr  9 10:24:19 2018: [error  ] K-1219: K-1216 may have already caused a loss of data in other tables.
Mon Apr  9 10:24:19 2018: [error  ] K-1221: Proceeding with --ignore-da and ignoring the schema update is technically possible, but data corruption may happen sooner or later.
Mon Apr  9 10:24:19 2018: [error  ] Can't update the database: Unable to upgrade database from version 8.6.80.0.68 to 8.6.80.0.70
Mon Apr  9 10:24:19 2018: [=======] Server shutdown complete.

      Server:
      Distributor ID: Debian
      Description: Debian GNU/Linux 8.10 (jessie)
      Release: 8.10
      Codename: jessie

      MySQL:
      mysql Ver 14.14 Distrib 5.5.59, for debian-linux-gnu (x86_64) using readline 6.3

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

        hello @lYSator,

        this line idicates a permission problem:

        Mon Apr  9 10:24:19 2018: [error  ] SQL [00026842] Failed: SELECT command denied to user 'zarafa'@'localhost' for table 'information_schema'

        check permissions of user zarafa. maybe the other errors are related.

        coffee_is_life

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

          @lysator said in Kopano core Update MySQL duplicated Entry:

          Mon Apr  9 10:24:19 2018: [error  ] K-1216: Cannot update to schema v69 because the "names" table contains unexpected rows.
Mon Apr  9 10:24:19 2018: [error  ] K-1217: Table fill level is indeterminate.
Mon Apr  9 10:24:19 2018: [error  ] K-1219: K-1216 may have already caused a loss of data in other tables.
Mon Apr  9 10:24:19 2018: [error  ] K-1221: Proceeding with --ignore-da and ignoring the schema update is technically possible, but data corruption may happen sooner or later.

          This is the core of the problem. We made some changes to the database-schema and this is causing some issues with your database.
          We are still working on improving and clarifying these messages.

          For now we suggest waiting with upgrading kopano-core (or following the mentioned steps).

          The kopano-core community packages have therefore been removed and I will upload a version without the schema changes later today.

          lYSator A Former User tommi 3 Replies Last reply Reply Quote 0
          • lYSator
            lYSator @Joost last edited by

            @joost okay so i’ll wait for your go to try the next package.

            1 Reply Last reply Reply Quote 0
            • A Former User
              A Former User @Joost last edited by

              This post is deleted!
              1 Reply Last reply Reply Quote 0
              • lYSator
                lYSator last edited by

                Version core-8.6.80.538_0+56-Debian_8.0-amd64 doesnt work for me

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

                  @lysator said in Kopano core Update MySQL duplicated Entry:

                  Version core-8.6.80.538_0+56-Debian_8.0-amd64 doesnt work for me

                  you’d have to be a bit more detailed. what does not work? are there any error messages?

                  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/

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

                    @fbartels sorry. got the same error.

                    Wed Apr 11 11:32:31 2018: [=======] Starting kopano-server version 8.6.80 (pid 12744)
Wed Apr 11 11:32:31 2018: [warning] Config warning: Option 'server_max_keep_alive_requests' is not used anymore.
Wed Apr 11 11:32:31 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.
Wed Apr 11 11:32:32 2018: [error  ] SQL [00031576] Failed: Duplicate entry '\x86\x03\x02\x00\x00\x00\x00\x00\xC0\x00\x00\x00\x00\x00\x00F-x-' 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`"
Wed Apr 11 11:32:32 2018: [error  ] KDatabase::I_Update() query failed: "Duplicate entry '\x86\x03\x02\x00\x00\x00\x00\x00\xC0\x00\x00\x00\x00\x00\x00F-x-' 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`
Wed Apr 11 11:32:32 2018: [error  ] K-1216: Cannot update to schema v69 because the "names" table contains unexpected rows.
Wed Apr 11 11:32:32 2018: [error  ] SQL [00031576] Failed: SELECT command denied to user 'zarafa'@'localhost' for table 'information_schema', Query Size: 104, Query: "SELECT `AUTO_INCREMENT` FROM mysql.information_schema WHERE table_schema="zarafa" AND table_name="names""
Wed Apr 11 11:32:32 2018: [error  ] KDatabase::DoSelect(): query failed: "SELECT command denied to user 'zarafa'@'localhost' for table 'information_schema'", query: SELECT `AUTO_INCREMENT` FROM mysql.information_schema WHERE table_schema="zarafa" AND table_name="names"
Wed Apr 11 11:32:32 2018: [error  ] K-1217: Table fill level is indeterminate.
Wed Apr 11 11:32:32 2018: [error  ] K-1219: K-1216 may have already caused a loss of data in other tables.
Wed Apr 11 11:32:32 2018: [error  ] K-1221: Proceeding with --ignore-da and ignoring the schema update is technically possible, but data corruption may happen sooner or later.
Wed Apr 11 11:32:32 2018: [error  ] Can't update the database: Unable to upgrade database from version 8.6.80.0.68 to 8.6.80.0.70
Wed Apr 11 11:32:32 2018: [=======] Server shutdown complete.```
                    1 Reply Last reply Reply Quote 0
                    • 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