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

    Damaged Kopano DB, partial restore with errors

    Kopano Groupware Core
    3
    19
    4203
    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.
    • olabre
      olabre last edited by olabre

      Hi there, I am struggeling with a damaged kopano DB. It themes that some process on outlook (i moved many many email) crashed by database and some disk error did another job. I tried to start th DB in recovery mode and was able to rescue parts of the data. No I have setup a clone of my mailserver (UCS 4.2.2.) VM and deleted the kopano DB and dumped is back from the rescued data. At least the DB starts without rescue mode. I started mysql and kopano server and was not able to login into webapp. It gives Unbekannter MAPI Fehler: MAPI_E_DISK_ERROR. Drives are OK, space is suffient, VM starts without problems.
      Mysql is running fine as well as Kopano-server.
      I managed to install phpmyadmin on that server to check database structure. As I spent many many days with that specific problem I reached my personal level of frustration. For sure there is no backup as we had this VM running for few days only (but imported much data).
      What else:
      kopano-admin --list-orphans says
      [crit ] CreateProfileTemp(): ConfigureMsgService failed 80040116: disk error
      Unable to open Admin session: disk error (0x80040116)

      kopano-admin -V
      Product version: 8,3,1,35
      File version: 35

      Any help is more than welcome.

      Olaf
      P.S. mysqlcheck gives no errors. Has anyone experience with percona repair tools? Maybe I can try a fix for the damaged DB using that tool.

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

        Hi @olabre ,

        @olabre said in Damaged Kopano DB, partial restore with errors:

        MAPI_E_DISK_ERROR

        is the error Kopano gives, if the server is not able to find the expected data in the database (so its not really related to the disk, but more the database).

        @olabre said in Damaged Kopano DB, partial restore with errors:

        able to rescue parts of the data

        which parts?

        In general I would say the outlook is grim unless you are not able to get a complete dump or your database or a prior full backup.

        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
        • olabre
          olabre last edited by

          Thanks Felix,
          I try to restore the database with innodb rescue mode but mysql looses connection on some point so I cannot dump the hole data. It is more or less the properties and tproperties table. The rest themes fine. Anyway when I try to import structure only it says missing primery key. Can you share a structure dump of the database?
          That would be helpful to go ahead.

          Olaf

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

            I have a version up and running - webapp is loading with progress bar. The query in phpmyadmin is

            SELECT hierarchy.id, properties.val_ulong FROM hierarchy LEFT JOIN properties ON properties.hierarchyid=hierarchy.id AND properties.tag=26512 AND properties.type=3 WHERE hierarchy.type=3 AND hierarchy.flags=2

            Db themes to be stuck.
            Log files says:

            Thu Sep 7 09:47:34 2017: [error ] KDatabase::Connect(): database access error -2147483641, mysql error: Lost connection to MySQL server a$
            Thu Sep 7 09:47:34 2017: [error ] KDatabsae::DoSelect(): query failed: SELECT hierarchy.id, properties.val_ulong FROM hierarchy LEFT JOIN$
            Thu Sep 7 09:47:34 2017: [error ] Unable to load searchfolders
            Thu Sep 7 09:47:35 2017: [ notice] Server shutdown complete.

            I am not sure if this matters but there is a typo: KDatabsae

            Olaf

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

              @olabre said in Damaged Kopano DB, partial restore with errors:

              I am not sure if this matters but there is a typo: KDatabsae

              That is just a typo in the log message. I created https://jira.kopano.io/browse/KC-804 to have this fixed.

              @olabre said in Damaged Kopano DB, partial restore with errors:

              It is more or less the properties and tproperties table.

              @olabre said in Damaged Kopano DB, partial restore with errors:

              SELECT hierarchy.id, properties.val_ulong FROM hierarchy LEFT JOIN properties ON properties.hierarchyid=hierarchy.id AND properties.tag=26512 AND properties.type=3 WHERE hierarchy.type=3 AND hierarchy.flags=2
              Db themes to be stuck.

              you query is reading from the incomplete properties table.

              You can find some information on the database structure in https://stash.kopano.io/projects/KC/repos/kopanocore/browse/doc/Database_structure.md

              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
              • olabre
                olabre last edited by olabre

                so this will mean that a non complete DB export wont work anyway - right?
                or is it more or less a structure issue?

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

                  @olabre yes, you cannot mix tables from different installations, as all of those tables are connected.

                  If you have a subscription you could also open up a support case to talk this issue through, but without a complete database dump there is nothing that can be done.

                  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
                  • mcostan
                    mcostan last edited by

                    Hi,

                    Do you have any errors in the mysql.log or that’s completely clean now?

                    When you recovered the data, did you use the innodb as per:

                    https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html

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

                      Hi, thanks for taking care. The support request was sent by company IT25 in Leipzig already. The innodb repair was used as link indicates. I had to set to level 4 - after some fixing level 2 works. In the end myql lost connection when dumping. Ibdata1 file has 10 GB of size.
                      On dumping mysql connections dies on specific rows. I tried to exclude that rows when dumping using --where in mysqldump command. As there are more than one rows affected it is a puzzle games as I have to find out the matching hierachyID for affected rows. This request dies in phpmaydmin for some rows so I cannot get full dump. But in the end this way might work when I am able to use more than one hierachyID in a single mysqldump.
                      mysql.log and mysql.err is empty

                      Olaf

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

                        Have you tried a higher level than 4?

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

                          I am not really sure but I think I had to use 5 once to get it started in the first step.

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

                            I experienced database crashes and corruptions in the past and I always was able to resolve it by using the lowest innodb recovery setting that would let me have a full dump.

                            Do you still have the original corrupted database? If yes, you could increase the innodb recovery settings till you are able to get a full dump without mysql to stop.

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

                              This is what I did starting with 1 - none worked except 5 - after this I was able to get back to 2

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

                                and if you let it at 5 and get a mysqldump would you have been able to get a full dump?

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

                                  this is the result:
                                  mysqldump --user=root -p"$(cat /etc/mysql.secret)" kopano --skip-extended-insert | gzip > dumpfilename_kopano_170907.sql.gz

                                  mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table properties at row: 2505854

                                  then I use aquire the hierachyID for that row using SELECT * FROM properties LIMIT 2505854, 1 and exclude this in the dump query as follows, hierarchyid is 230503

                                  mysqldump --user=root -p"$(cat /etc/mysql.secret)" kopano properties --where “hierarchyid!=230503” | gzip > dumpfilename_kopano_prop_170907_1.sql.gz

                                  Result: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table properties at row: 3796811

                                  I query: SELECT * FROM properties LIMIT 3796811, 1
                                  this call will come back with connection lost

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

                                    it looks like a bad crash… I am sorry if you tried the possible highest innodb recovery options and you still get the corruption then I am afraid I am out of ideas.

                                    In general (once this is resolved) I would use a script (I think I found it previously on the zarafa forums or guides or something) that dumps the database every night automatically and keeps it for a number of days, you can run this twice a day (which I do).

                                    I also go one extreme further and turn off the whole thing during the night and take a dump of what’s in /var/lib/msyql + a copy of the entire VM that runs the database.

                                    The system is therefore down for like 30 minutes overnight.

                                    Perhaps a bit extreme, but the worse that can happen if everything is corrupted is that I lose 1/5 day of emails or at worse a day.

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

                                      @mcostan you should have a look at kopano-backup to complement your backup strategy.

                                      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
                                      • mcostan
                                        mcostan last edited by

                                        @fbartels said in Damaged Kopano DB, partial restore with errors:

                                        kopano-backup

                                        thanks, I’ll have a look.

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

                                          well yes, the backup was next on our task list - the way is clear either VM snapshot or full backup or mysql dump - but we did not get that far

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