Hi, Multiserver setup: 2 web servers, 1 database server, 2 name servers. The 1st web server (web01) is the master. After upgrading Ubuntu 12.04LTS->14.04LTS->16.04LTS and ISPC 3.0.4 -> 3.1.13, I had lots of errors on missing columns in various dbispconfig tables. I have manually corrected / added these, and I think I now am up to speed on those. But I have a problem with alias domains. If the parent website is on the second web server (web02), the necessary changes is not written to the parent website vhost file. I can see by the timestamp that the file is opened and saved, but no changes is actually made. The alias domain is correctly inserted in DB table dbispconfig -> web_domains. If the parent web site is on the master server the changes are written correctly. Also, if i remove an alias domain which parent website is on web02, the changes are made correctly (e.g. the alias domain is removed from the parent website's vhost file.) Where should I start troubleshooting this issue? Regards Christian
You can use the debug mode to see in detail what happens on that node. Enable debug log on the master for this slave, comment out server.sh cronjob on the slave, add an alias domain and then run server.sh on the slave to get the detailed output for this action. https://www.faqforge.com/linux/debugging-ispconfig-3-server-actions-in-case-of-a-failure/
The debug gives an SQL warning "Unknown column 'rewrite_to_https' in 'field list'", but as far as I can see the column is there... It is present in the dbispconfig table web_domain when I look at it in phpMyAdmin. Here's the output from debug: 28.01.2019-12:46 - DEBUG - Found 1 changes, starting update process. 28.01.2019-12:46 - WARNING - Falsche Anfrage / Wrong QuerySQL-Query = REPLACE INTO `web_domain` (`domain_id`,`sys_userid`,`sys_groupid`,`sys_perm_user`,`sys_perm_group`,`sys_perm_other`,`server_id`,`ip_address`,`ipv6_address`,`domain`,`type`,`parent_domain_id`,`vhost_type`,`document_root`,`web_folder`,`system_user`,`system_group`,`hd_quota`,`traffic_quota`,`cgi`,`ssi`,`suexec`,`errordocs`,`is_subdomainwww`,`subdomain`,`php`,`ruby`,`python`,`perl`,`redirect_type`,`redirect_path`,`seo_redirect`,`rewrite_to_https`,`ssl`,`ssl_letsencrypt_exclude`,`ssl_letsencrypt`,`ssl_state`,`ssl_locality`,`ssl_organisation`,`ssl_organisation_unit`,`ssl_country`,`ssl_domain`,`ssl_request`,`ssl_cert`,`ssl_bundle`,`ssl_key`,`ssl_action`,`stats_password`,`stats_type`,`allow_override`,`apache_directives`,`nginx_directives`,`php_fpm_use_socket`,`pm`,`pm_max_children`,`pm_start_servers`,`pm_min_spare_servers`,`pm_max_spare_servers`,`pm_process_idle_timeout`,`pm_max_requests`,`php_open_basedir`,`custom_php_ini`,`backup_interval`,`backup_copies`,`backup_excludes`,`active`,`traffic_quota_lock`,`fastcgi_php_version`,`proxy_directives`,`last_quota_notification`,`rewrite_rules`,`added_date`,`added_by`,`directive_snippets_id`,`enable_pagespeed`,`http_port`,`https_port`,`folder_directive_snippets`,`log_retention`) VALUES ('613','1','111','riud','riud','','2',NULL,NULL,'renturglede.com','alias','228',NULL,NULL,NULL,NULL,NULL,'0','-1','y','y','y','1','1','www','y','n','n','n','','','','n','n','n','n',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'webalizer','All',NULL,NULL,'y','dynamic','10','2','1','5','10','0',NULL,NULL,'none','1',NULL,'y','n',NULL,NULL,NULL,NULL,NULL,NULL,'0','n','80','443',NULL,'10') -> 1054 (Unknown column 'rewrite_to_https' in 'field list') 28.01.2019-12:46 - DEBUG - Replicated from master: REPLACE INTO `web_domain` (`domain_id`,`sys_userid`,`sys_groupid`,`sys_perm_user`,`sys_perm_group`,`sys_perm_other`,`server_id`,`ip_address`,`ipv6_address`,`domain`,`type`,`parent_domain_id`,`vhost_type`,`document_root`,`web_folder`,`system_user`,`system_group`,`hd_quota`,`traffic_quota`,`cgi`,`ssi`,`suexec`,`errordocs`,`is_subdomainwww`,`subdomain`,`php`,`ruby`,`python`,`perl`,`redirect_type`,`redirect_path`,`seo_redirect`,`rewrite_to_https`,`ssl`,`ssl_letsencrypt_exclude`,`ssl_letsencrypt`,`ssl_state`,`ssl_locality`,`ssl_organisation`,`ssl_organisation_unit`,`ssl_country`,`ssl_domain`,`ssl_request`,`ssl_cert`,`ssl_bundle`,`ssl_key`,`ssl_action`,`stats_password`,`stats_type`,`allow_override`,`apache_directives`,`nginx_directives`,`php_fpm_use_socket`,`pm`,`pm_max_children`,`pm_start_servers`,`pm_min_spare_servers`,`pm_max_spare_servers`,`pm_process_idle_timeout`,`pm_max_requests`,`php_open_basedir`,`custom_php_ini`,`backup_interval`,`backup_copies`,`backup_excludes`,`active`,`traffic_quota_lock`,`fastcgi_php_version`,`proxy_directives`,`last_quota_notification`,`rewrite_rules`,`added_date`,`added_by`,`directive_snippets_id`,`enable_pagespeed`,`http_port`,`https_port`,`folder_directive_snippets`,`log_retention`) VALUES ('613','1','111','riud','riud','','2',NULL,NULL,'renturglede.com','alias','228',NULL,NULL,NULL,NULL,NULL,'0','-1','y','y','y','1','1','www','y','n','n','n','','','','n','n','n','n',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'webalizer','All',NULL,NULL,'y','dynamic','10','2','1','5','10','0',NULL,NULL,'none','1',NULL,'y','n',NULL,NULL,NULL,NULL,NULL,NULL,'0','n','80','443',NULL,'10') 28.01.2019-12:46 - DEBUG - Calling function 'ssl' from plugin 'apache2_plugin' raised by event 'web_domain_insert'. 28.01.2019-12:46 - DEBUG - Calling function 'insert' from plugin 'apache2_plugin' raised by event 'web_domain_insert'. 28.01.2019-12:46 - DEBUG - Add server alias: roroskraft.no 28.01.2019-12:46 - DEBUG - Add server alias: roros-kraft.no 28.01.2019-12:46 - DEBUG - Add server alias: xn--rroskraft-l8a.no 28.01.2019-12:46 - DEBUG - Add server alias: xn--rros-kraft-0cb.no 28.01.2019-12:46 - DEBUG - Add server alias: revelektro.no 28.01.2019-12:46 - DEBUG - Add server alias: rorosenergi.no 28.01.2019-12:46 - DEBUG - Add server alias: revkraft.no 28.01.2019-12:46 - DEBUG - Add server alias: revinstallasjon.no 28.01.2019-12:46 - DEBUG - Add server alias: revinst.no 28.01.2019-12:46 - DEBUG - Add server alias: hyttadi.no 28.01.2019-12:46 - DEBUG - Creating fastcgi starter script: /var/www/php-fcgi-scripts/web228/.php-fcgi-starter 28.01.2019-12:46 - DEBUG - Enable SSL for: rev.no 28.01.2019-12:46 - DEBUG - Writing the vhost file: /etc/apache2/sites-available/rev.no.vhost 28.01.2019-12:46 - DEBUG - Apache status is: running 28.01.2019-12:46 - DEBUG - Calling function 'restartHttpd' from module 'web_module'. 28.01.2019-12:46 - DEBUG - Restarting httpd: systemctl restart apache2.service 28.01.2019-12:46 - DEBUG - Apache restart return value is: 0 28.01.2019-12:46 - DEBUG - Apache online status after restart is: running 28.01.2019-12:46 - DEBUG - Processed datalog_id 20174 28.01.2019-12:46 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock finished.
Ok, so the slave has its own database? I thought it was using the master. That database isn't available in phpMyAdmin I guess, so I have to issue the commands to insert this column on the CLI. You don't have the command to do this easily available, by any chance? I'm no CLI mysql expert...
Code: ALTER TABLE `web_domain` ADD COLUMN `rewrite_to_https` ENUM('y','n') NOT NULL DEFAULT 'n' AFTER `seo_redirect`; I wonder why columns are missing in your database at all as the sql statements to update the database contain e.g. this column. Is the root password in /usr/local/ispconfig/server/conf/mysql_clientdb.conf wrong on the servers?
Upon issuing the command you sent, I get "ERROR 1067 (42000): Invalid default value for 'added_date'" I got the same error in ISPC GUI after the upgrades, but was able to correct them through phpMyAdmin. It seems something has gone terribly wrong during the updates, but still, the system works for the most part. Something I can do / some script to run to be sure all the database schemas are up to date and correct? Or do I have to install a brand new multiserver setup and migrate all clients, dns zones and webs?
A reinstall should not be nescessary. What you might try is this: 1) backup the old ispconfig database, just in case something goes wrong. 2) Export the data (not the table definitions, just the data) as sql, the export must contain the column names, so either "INSERT INTO tbl_name (col_A,col_B) VALUES (1,2,3), (4,5,6), (7,8,9)" or "INSERT INTO tbl_name (col_A,col_B,col_C) VALUES (1,2,3)" as format. 3) Delete all tables in the database. 4) import the install/sql/ispconfig3.sql file from ispconfig tar.gz into the database. 5) Empty all tables (truncate), so that only the table structures remain. 6) import the original data from (2) This should give you the complete up to date structure with your original data. As alternative, you can try to run the install/sql/incremental/ files in the db. This will give you a bunch of errors but it should add all missing columns as well. If you want to use that way, then we will have to find out what the last installed incremental file of your 3.0.4 version was.
I've tried your recipe now, and all goes well until 6), which gives me the following error: #1054 - Unknown column 'default_mailserver' in 'field list'
The column default_mailserver exists in the install/sql/ispconfig3.sql file, so I wonder why it is missing in your database. Please check the 'client' table, does it really not contain a column named default_mailserver?
The 'client' table contains the column 'default_mailserver', and all the client data is imported into this table, but import stops there. All tables AFTER 'client' is empty, as the imports halts on this error.
Hmm, maybe we had a column with that name in the client_template table as well. Normally we don't remove tables but it might be that we did that in this case. Please run these queries in the new database, then empty the tables and try the data import again. Code: ALTER TABLE `client_template` ADD `default_mailserver` INT(11) NOT NULL DEFAULT 1; ALTER TABLE `client_template` ADD `default_webserver` INT(11) NOT NULL DEFAULT 1; ALTER TABLE `client_template` ADD `default_dnsserver` INT(11) NOT NULL DEFAULT 1; ALTER TABLE `client_template` ADD `default_slave_dnsserver` INT(11) NOT NULL DEFAULT 1; ALTER TABLE `client_template` ADD `default_dbserver` INT(11) NOT NULL DEFAULT 1; ALTER TABLE `client_template` ADD COLUMN `default_xmppserver` int(11) unsigned NOT NULL DEFAULT '1';
Ahh, then the data import worked! `default_mailserver`, `default_webserver`, `default_dnsserver` and `default_dbserver` was missing from the table 'client_template'. So now, I guess I have the correct tables on my master server, but what about the other servers in my multiserver setup? And what about the mysteriously missing /usr/local/ispconfig/server/conf/mysql_clientdb.conf?
I guess you will have to do the same steps on the slaves in case they miss columns too. And the clientdb_conf file path is /usr/local/ispconfig/server/lib/mysql_clientdb.conf, I'm sorry for posting a wrong path.
Ok, and this goes for ALL the slaves, the web database server and the name servers also? Is it possible to add these to phpMyAdmin? As of now, I can choose between "webserver" and "databaseserver" on the PMA login screen. Can the rest be added in /etc/phpmyadmin/config.inc.php to facilitate this as well? I found the mysql_clientdb.conf on my servers now, and it is correct.
I found out how to do it. I will try your recipe on my "web02" server now, to see if I can fix my alias domain problem. Thank you for your help
