ISPConfig reverting back to 3.2.5 from 3.2.6 (CentOS 6)

Discussion in 'Tips/Tricks/Mods' started by jnewman67, Oct 1, 2021.

  1. jnewman67

    jnewman67 Active Member HowtoForge Supporter

    In case you need to restore your ISPConfig 3.2.5 installation after a failed 3.2.6 upgrade, the database structure has changed, and importing the .sql backup file created during the upgrade won't work as a straight import. There are 4 tables that were removed from the database, and those need to be recreated before the import of the database will work. Also, I found instructions elsewhere that indicates you'll need to truncate all the tables in the database before importing the data. So here's what I did within the command-line for MYSQL on a CentOS 6 machine...

    1. launch mysql with the user and password your installation requires
    2. "use dbispconfig;" to change databases.
    3. create the deleted tables:
    CREATE TABLE `software_package` (
    `package_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `software_repo_id` int(11) unsigned NOT NULL DEFAULT '0',
    `package_name` varchar(64) NOT NULL DEFAULT '',
    `package_title` varchar(64) NOT NULL DEFAULT '',
    `package_description` text,
    `package_version` varchar(8) DEFAULT NULL,
    `package_type` enum('ispconfig','app','web') NOT NULL DEFAULT 'app',
    `package_installable` enum('yes','no','key') NOT NULL DEFAULT 'yes',
    `package_requires_db` enum('no','mysql') NOT NULL DEFAULT 'no',
    `package_remote_functions` text,
    `package_key` varchar(255) NOT NULL DEFAULT '',
    `package_config` text,
    PRIMARY KEY (`package_id`),
    UNIQUE KEY `package_name` (`package_name`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    CREATE TABLE `software_repo` (
    `software_repo_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `sys_userid` int(11) unsigned NOT NULL DEFAULT '0',
    `sys_groupid` int(11) unsigned NOT NULL DEFAULT '0',
    `sys_perm_user` varchar(5) DEFAULT NULL,
    `sys_perm_group` varchar(5) DEFAULT NULL,
    `sys_perm_other` varchar(5) DEFAULT NULL,
    `repo_name` varchar(64) DEFAULT NULL,
    `repo_url` varchar(255) DEFAULT NULL,
    `repo_username` varchar(64) DEFAULT NULL,
    `repo_password` varchar(64) DEFAULT NULL,
    `active` enum('n','y') NOT NULL DEFAULT 'y',
    PRIMARY KEY (`software_repo_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

    CREATE TABLE `software_update` (
    `software_update_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `software_repo_id` int(11) unsigned NOT NULL DEFAULT '0',
    `package_name` varchar(64) NOT NULL DEFAULT '',
    `update_url` varchar(255) NOT NULL DEFAULT '',
    `update_md5` varchar(255) NOT NULL DEFAULT '',
    `update_dependencies` varchar(255) NOT NULL DEFAULT '',
    `update_title` varchar(64) NOT NULL DEFAULT '',
    `v1` tinyint(1) NOT NULL DEFAULT '0',
    `v2` tinyint(1) NOT NULL DEFAULT '0',
    `v3` tinyint(1) NOT NULL DEFAULT '0',
    `v4` tinyint(1) NOT NULL DEFAULT '0',
    `type` enum('full','update') NOT NULL DEFAULT 'full',
    PRIMARY KEY (`software_update_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    CREATE TABLE `software_update_inst` (
    `software_update_inst_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `software_update_id` int(11) unsigned NOT NULL DEFAULT '0',
    `package_name` varchar(64) NOT NULL DEFAULT '',
    `server_id` int(11) unsigned NOT NULL DEFAULT '0',
    `status` enum('none','installing','installed','deleting','deleted','failed') NOT NULL DEFAULT 'none',
    PRIMARY KEY (`software_update_inst_id`),
    UNIQUE KEY `software_update_id` (`software_update_id`,`package_name`,`server_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;​

    4. truncate the tables:
    truncate table aps_instances;
    truncate table aps_instances_settings;
    truncate table aps_packages;
    truncate table aps_settings;
    truncate table attempts_login;
    truncate table client;
    truncate table client_circle;
    truncate table client_message_template;
    truncate table client_template;
    truncate table client_template_assigned;
    truncate table country;
    truncate table cron;
    truncate table directive_snippets;
    truncate table dns_rr;
    truncate table dns_slave;
    truncate table dns_soa;
    truncate table dns_ssl_ca;
    truncate table dns_template;
    truncate table domain;
    truncate table firewall;
    truncate table ftp_traffic;
    truncate table ftp_user;
    truncate table help_faq;
    truncate table help_faq_sections;
    truncate table iptables;
    truncate table mail_access;
    truncate table mail_backup;
    truncate table mail_content_filter;
    truncate table mail_domain;
    truncate table mail_forwarding;
    truncate table mail_get;
    truncate table mail_mailinglist;
    truncate table mail_relay_recipient;
    truncate table mail_traffic;
    truncate table mail_transport;
    truncate table mail_user;
    truncate table mail_user_filter;
    truncate table monitor_data;
    truncate table openvz_ip;
    truncate table openvz_ostemplate;
    truncate table openvz_template;
    truncate table openvz_traffic;
    truncate table openvz_vm;
    truncate table remote_session;
    truncate table remote_user;
    truncate table server;
    truncate table server_ip;
    truncate table server_ip_map;
    truncate table server_php;
    truncate table shell_user;
    truncate table spamfilter_policy;
    truncate table spamfilter_users;
    truncate table spamfilter_wblist;
    truncate table support_message;
    truncate table sys_config;
    truncate table sys_cron;
    truncate table sys_datalog;
    truncate table sys_dbsync;
    truncate table sys_filesync;
    truncate table sys_group;
    truncate table sys_ini;
    truncate table sys_log;
    truncate table sys_remoteaction;
    truncate table sys_session;
    truncate table sys_theme;
    truncate table sys_user;
    truncate table web_backup;
    truncate table web_database;
    truncate table web_database_user;
    truncate table web_domain;
    truncate table web_folder;
    truncate table web_folder_user;
    truncate table web_traffic;
    truncate table webdav_user;
    truncate table xmpp_domain;
    truncate table xmpp_user;
    truncate table software_package;
    truncate table software_repo;
    truncate table software_update;
    truncate table software_update_inst;​

    then you can import the SQL backup file created during the upgrade (/var/backup/ispconfig_db_backup.sql).

    to complete the revert, you'll need to restore the /etc and /usr/local/ispconfig folders from the zipped backup files
    cd /
    tar xzf /var/backup/thebackupfolder/etc.tar.gz
    tar xzf /var/backup/thebackupfolder/ispconfig_software.tar.gz​
    then restart the server
    In my case, postfix was not processing mail, so I had to tell it to reprocess the mail queue manually:
    postqueue -f​
    to get things working again

    hope someone else finds this useful - i couldn't find specific instructions, and recreating missing tables makes it very unfriendly if you haven't played with them before, or don't have another type of SQL backup to pull them from (i use mysqldumps to back up all the DBs on my server, which includes the table creating commands, so I happened to have them)
     

Share This Page