Kopano core Update MySQL duplicated Entry

fbartels

@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?

lYSator

@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.```

klipp

same problem here

tommi

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.

Gerald

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

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

Gerald

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!

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.

lYSator

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

Thank you!

Coffee_is_life

@lYSator,

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

coffee_is_life

lYSator

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

Jo-TL

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