errors trying restore DB

Discussion in 'Installation/Configuration' started by qmax, Dec 21, 2020.

  1. qmax

    qmax New Member

    I had this kind of errors when i executed this command

    [email protected]:~# mysql -u root dbispconfig -p < /var/backup/ispconfig_mail.xxxxxxx.com_2020-10-22_13-49/ispconfig_db_backup.sql
    ERROR 1062 (23000) at line 71: Duplicate entry '1' for key 'PRIMARY'

    The Backup DB start with:
    ---------------------------------------------
    -- MySQL dump 10.13 Distrib 5.5.61, for debian-linux-gnu (x86_64)
    --
    -- Host: localhost Database: dbispconfig
    -- ------------------------------------------------------
    -- Server version 5.5.54-0ubuntu0.12.04.1
    ----------------------------------------------

    and the new server is
    Ubuntu 16.04.7 LTS (GNU/Linux 3.8.0-44-generic x86_64)
    Server version: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
     
  2. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    You could examine line 71 and maybe remove the duplicate primary key. Strange though the dump would create such error.
    Did the database already contain data before you read that dump file in? Maybe the primary key existed already?
     
  3. till

    till Super Moderator Staff Member ISPConfig Developer

    That's probably the most likely resaon indeed. Before you import the backup, truncate (empty, not delete) all database tables. But in case you need the old data, make a backup of the current data first.
     
  4. qmax

    qmax New Member

    Really Thank you , i forced the restore with this line
    mysql -f -u root dbispconfig -p
    then i rewrote de ispconfig folder with que backup compress file

    Now i have acces again to ispconfig panel , mail accounts and dns are working but....

    I have no any website at Site tab in ispconfig panel , why that ?

    thanks aganin for your help
     
  5. qmax

    qmax New Member

    im sorry , i get this errors when i executed with " -f " (force) , i will try Truncate ( not delete ) that tables and will try again


    [email protected]:~# mysql -f -u root dbispconfig -p < /var/backup/ispconfig_xxxxxxxxxxx_2020-10-22_13-49/ispconfig_db_backup.sql
    Enter password:
    ERROR 1062 (23000) at line 71: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 117: Duplicate entry 'AF' for key 'PRIMARY'
    ERROR 1062 (23000) at line 145: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 164: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 174: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 184: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 203: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 213: Duplicate entry '2014-07-11' for key 'hostname'
    ERROR 1062 (23000) at line 223: Duplicate entry '3' for key 'PRIMARY'
    ERROR 1062 (23000) at line 233: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 243: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 289: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 335: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 354: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 364: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 393: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 403: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 449: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 459: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 478: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 506: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 534: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 544: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 572: Duplicate entry 'db-db_version' for key 'PRIMARY'
    ERROR 1062 (23000) at line 582: Duplicate entry 'cronjob_mailbox_stats' for key 'PRIMARY'
    ERROR 1062 (23000) at line 592: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 620: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 630: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 649: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 669: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 679: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 698: Duplicate entry '1' for key 'PRIMARY'
    ERROR 1062 (23000) at line 708: Duplicate entry '2' for key 'PRIMARY'
    ERROR 1054 (42S22) at line 718: Unknown column 'fastcgi_php_version' in 'field list'
    ERROR 1062 (23000) at line 746: Duplicate entry 'costelho.com.ar-2013-10-03' for key 'hostname'
     
  6. qmax

    qmax New Member

    well , i can Truncated all tables and import again the backup , only 1 error now :

    ERROR 1054 (42S22) at line 718: Unknown column 'fastcgi_php_version' in 'field list'

    And stiil no website at "sites" tab
     
  7. Jesse Norell

    Jesse Norell ISPConfig Developer Staff Member ISPConfig Developer

    That means the db schema of the live database doesn't match the data you are restoring to it. Find a schema from the same version as your sql dump and load that.
     
  8. qmax

    qmax New Member

    Hi Jesse , i create the columns and then i can restore the complete db
     
  9. Jesse Norell

    Jesse Norell ISPConfig Developer Staff Member ISPConfig Developer

    The problem with that is there is no fastcgi_php_version field in the current sql schema. My guess is that the database you're restoring to is "current" and your sql dump is using an older schema - so you just created and loaded data into a column which will never be used by your ISPConfig ui/server plugins/etc. You could try manually updating things according to the incremental sql update files, or just run through and edit each of your domains to make sure it has correct settings, and doing either, keep an eye out for sql errors that may need fixed. Or go back to an older schema and load your data into that, then do a normal ispconfig update.
     

Share This Page