Group: Member Post Group: Newbie
Posts: 1
Status:  |
Hi,
We have encountered the same error in our company, where we run several Virtualizor servers.
We opened a ticket and provided root access. However, it took a considerable amount of time for them to respond, which caused our support team to become overwhelmed since there were adjustments that needed to be made via the API or web interface. Unfortunately, none of these adjustments were functioning correctly.
After a few days, they informed us that we needed to pay $200 for a Softaculous/Virtualizor fix to resolve the problem. Over the last few years, we have spent over $25,000 on Softaculous/Virtualizor licenses. It's frustrating that when we require support, they charge us extra for what seems like a simple upgrade issue.
We are well aware that automatic updates are the best configuration, and we are unsure why these servers are not set to automatic updates.
As we have many Virtualizor servers, we investigated the problem and found that the problematic server was updating the PHP files but not the MySQL structure.
First, we attempted to locate any .sql file in the Virtualizor files that provided upgrades between versions, as is customary with most software suppliers. However, we were unable to find it easily. So, we used mysqldiff to compare the MySQL structure between an updated Virtualizor server that was working correctly and the problematic MySQL Virtualizor server.
Here are the steps we took and the mysqldiff result for our case:
1. Get the mysql root password using the following command:
Code grep dbpass /usr/local/virtualizor/universal.php
2. Make full backup of virtualizor database:
Code /usr/local/emps/bin/mysqldump virtualizor -u root -p'ROOT-PASS-HERE' > /root/mysql-virtualizor-database.sql
3. Make sure virtualizor backup is fine:
Code vi /root/mysql-virtualizor-database.sql
4. Connect to database and run all alter tables listed by mysqldiff.
Code /usr/local/emps/bin/mysql virtualizor -u root -p'ROOT-PASS-HERE'
Code # This first alter table fix the main error, but after that we realized there are a lot of changes
ALTER TABLE servers MODIFY COLUMN locked text, MODIFY COLUMN ha_master int(10) NOT NULL DEFAULT '0', ADD COLUMN sys_load float(6,2) NOT NULL, ADD COLUMN data text NOT NULL;
ALTER TABLE `virtualizor`.`admin_acl`
ADD COLUMN act_load_balancer tinyint(4) NOT NULL DEFAULT '0',
ADD COLUMN act_show_api_log tinyint(4) NOT NULL DEFAULT '0',
ADD COLUMN act_volumes tinyint(4) NOT NULL DEFAULT '0',
ADD COLUMN act_api_credential_edit tinyint(4) NOT NULL DEFAULT '0',
ADD COLUMN act_list_api tinyint(4) NOT NULL DEFAULT '0',
ADD COLUMN act_terminal tinyint(4) NOT NULL DEFAULT '0' AFTER act_add_dnsrecord,
ADD COLUMN act_manage_load_balancer tinyint(4) NOT NULL DEFAULT '0',
ADD COLUMN act_create_api tinyint(4) NOT NULL DEFAULT '0',
ADD COLUMN act_sso tinyint(4) NOT NULL DEFAULT '0';
ALTER TABLE `virtualizor`.`api`
DROP PRIMARY KEY,
DROP INDEX apikey,
ADD PRIMARY KEY(`idapi`),
ADD UNIQUE INDEX apikey (apikey),
ADD COLUMN ip text NULL AFTER apipass,
ADD COLUMN logging int(11) NULL DEFAULT '0',
CHANGE COLUMN data data text NOT NULL;
ALTER TABLE `virtualizor`.`backup_plans`
ADD COLUMN data text NULL AFTER io_limit;
ALTER TABLE `virtualizor`.`backup_servers`
ADD COLUMN sshpub_key text NULL, LTER TABLE `virtualizor`.`backup_plans`
ADD COLUMN data text NULL AFTER io_limit;
ADD COLUMN ssh_key int(1) NULL AFTER password,
ADD COLUMN sshpri_key text NULL,
CHANGE COLUMN ftps ftps int(1) NULL DEFAULT '0' AFTER dir,
CHANGE COLUMN salt salt varchar(20) NOT NULL,
CHANGE COLUMN port port int(11) NOT NULL AFTER salt,
CHANGE COLUMN dir dir text NOT NULL AFTER port;
ALTER TABLE `virtualizor`.`bandwidth`
DROP INDEX vpsid,
ADD UNIQUE INDEX vpsid (device,time,date,vpsid),
ADD COLUMN c_in bigint(12) NOT NULL DEFAULT '0' AFTER out,
ADD COLUMN c_out bigint(12) NOT NULL DEFAULT '0';
ALTER TABLE `virtualizor`.`disks`
DROP PRIMARY KEY,
DROP INDEX disk_uuid,
ADD PRIMARY KEY(`did`),
ADD UNIQUE INDEX disk_uuid (disk_uuid),
ADD COLUMN mnt_point text NULL,
ADD COLUMN user_uid int(10) NOT NULL DEFAULT '0' AFTER bus_driver_num,
ADD COLUMN disk_name text NULL;
ALTER TABLE `virtualizor`.`invoices`
ADD COLUMN tax text NULL AFTER net,
CHANGE COLUMN token token varchar(255) NOT NULL,
CHANGE COLUMN cancelled cancelled int(10) NOT NULL DEFAULT '0' AFTER token;
ALTER TABLE `virtualizor`.`ip_logs`
DROP PRIMARY KEY,
ADD PRIMARY KEY(`iplid`);
ALTER TABLE `virtualizor`.`ippool`
ADD COLUMN pdns_id int(11) NOT NULL DEFAULT '0',
ADD COLUMN ovs varchar(100) NOT NULL,
ADD COLUMN vlan_tag int(11) NOT NULL DEFAULT '0' AFTER vlan,
ADD COLUMN ovs_bridge varchar(100) NOT NULL,
ADD COLUMN uid int(11) NOT NULL DEFAULT '0' AFTER mtu,
CHANGE COLUMN vlan vlan int(11) NOT NULL DEFAULT '0';
ALTER TABLE `virtualizor`.`ips`
DROP PRIMARY KEY,
DROP INDEX vpsid,
ADD PRIMARY KEY(`ipid`),
ADD INDEX vpsid (vpsid),
ADD INDEX ippid_index (ippid);
ALTER TABLE `virtualizor`.`logs_admin`
DROP PRIMARY KEY,
ADD PRIMARY KEY(`actid`);
ALTER TABLE `virtualizor`.`logs_vps`
DROP PRIMARY KEY,
ADD PRIMARY KEY(`actid`);
ALTER TABLE `virtualizor`.`plans`
DROP PRIMARY KEY,
ADD PRIMARY KEY(`plid`),
ADD COLUMN load_balancer varchar(50) NULL DEFAULT '0' AFTER webuzo_data,
ADD COLUMN data text NULL;
ALTER TABLE `virtualizor`.`recipes`
ADD COLUMN admin_only int(2) NOT NULL DEFAULT '0' AFTER status;
ALTER TABLE `virtualizor`.`ssh_keys`
DROP PRIMARY KEY,
DROP INDEX uuid,
ADD PRIMARY KEY(`keyid`),
ADD UNIQUE INDEX uuid (uuid);
ALTER TABLE `virtualizor`.`storage`
ADD COLUMN project_name text NULL AFTER primary_storage,
CHANGE COLUMN last_alert last_alert int(10) NOT NULL DEFAULT '0';
ALTER TABLE `virtualizor`.`tasks`
DROP PRIMARY KEY,
ADD PRIMARY KEY(`actid`);
ALTER TABLE `virtualizor`.`user_plans`
ADD COLUMN read_bytes_sec bigint(20) NOT NULL DEFAULT '0',
ADD COLUMN space_per_vm int(10) NOT NULL DEFAULT '0' AFTER date_created,
ADD COLUMN write_bytes_sec bigint(20) NOT NULL DEFAULT '0',
ADD COLUMN total_iops_sec bigint(20) NOT NULL DEFAULT '0';
ALTER TABLE `virtualizor`.`users`
DROP INDEX email,
DROP PRIMARY KEY,
ADD PRIMARY KEY(`uid`),
ADD INDEX email (email),
ADD COLUMN read_bytes_sec bigint(20) NOT NULL DEFAULT '0',
ADD COLUMN space_per_vm int(10) NOT NULL DEFAULT '0' AFTER webuzo_prem_apps,
ADD COLUMN write_bytes_sec bigint(20) NOT NULL DEFAULT '0',
ADD COLUMN total_iops_sec bigint(20) NOT NULL DEFAULT '0';
ALTER TABLE `virtualizor`.`vps`
DROP PRIMARY KEY,
ADD PRIMARY KEY(`vpsid`),
ADD INDEX uid (uid),
ADD COLUMN load_balancer varchar(50) NULL DEFAULT '0' AFTER ha,
ADD COLUMN plan_expiry int(11) NOT NULL DEFAULT '0' AFTER data,
CHANGE COLUMN data data text NULL;
CREATE TABLE `passthrough` (
`pid` int(11) NOT NULL AUTO_INCREMENT,
`dev_name` varchar(255) DEFAULT NULL COMMENT 'PCI device specific',
`dev_fullname` text NOT NULL COMMENT 'Full info about device',
`name` varchar(255) NOT NULL,
`vpsid` int(10) DEFAULT '0',
`serid` int(10) DEFAULT '0',
`iommu_grp_num` int(10) DEFAULT '0' COMMENT 'IOMMU group number. Only for PCI devices',
`iommu_peers_num` int(10) DEFAULT '0' COMMENT 'No. of Iommu group peers',
`description` text,
`type` varchar(255) NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`pid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `load_balancer` (
`lbid` int(11) NOT NULL AUTO_INCREMENT,
`vps_uuid` varchar(50) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
`sgid` int(11) DEFAULT NULL,
`settings` text,
PRIMARY KEY (`lbid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; |
