Another 1054 (Unknown column 'php_fpm_chroot' in 'field list')

Discussion in 'ISPConfig 3 Priority Support' started by jolt, Dec 11, 2019.

  1. jolt

    jolt New Member

    Hi!

    I'm running a multi-server setup, and this just started to happen to me. I have tried to verify the DB, and php_fpm_chroot does exist on both master and clients. I have also run the update.php to reconfigure the db, but that made no difference. Both systems are running 3.1.15p2

    Any guesses?

    WARNING - Falsche Anfrage / Wrong QuerySQL-Query = REPLACE INTO `web_domain` (`domain_id` ...... ) -> 1054 (Unknown column 'php_fpm_chroot' in 'field list')
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Not really, when you say the column is there on all servers. According to the error message, the column is missing on the slave server or at lesat it was missing at the time the error happened.
     
  3. jolt

    jolt New Member

    I logged in and made a diff of the schema of the two servers, and they are the same, no sure what I can do. As soon as I change something on the domain I get the same error over and over. :/
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    Try to run the sql queries from the install/sql/incremental/upd_0087.sql file manually with phpmyadmin in the database of the slave server. You might get some errors, but that's ok.

    Here the queries:

    Code:
    ALTER TABLE `sys_datalog` ADD `session_id` varchar(64) NOT NULL DEFAULT '' AFTER `error`;
    ALTER TABLE `sys_user` CHANGE `sys_userid` `sys_userid` INT(11) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'Created by userid';
    ALTER TABLE `sys_user` CHANGE `sys_groupid` `sys_groupid` INT(11) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'Created by groupid';
    ALTER TABLE `web_domain` ADD COLUMN `php_fpm_chroot` enum('n','y') NOT NULL DEFAULT 'n' AFTER `php_fpm_use_socket`;
    
    CREATE TABLE IF NOT EXISTS `dns_ssl_ca` (
      `id` int(10) 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) NOT NULL DEFAULT '',
      `sys_perm_group` varchar(5) NOT NULL DEFAULT '',
      `sys_perm_other` varchar(5) NOT NULL DEFAULT '',
      `active` enum('N','Y') NOT NULL DEFAULT 'N',
      `ca_name` varchar(255) NOT NULL DEFAULT '',
      `ca_issue` varchar(255) NOT NULL DEFAULT '',
      `ca_wildcard` enum('Y','N') NOT NULL DEFAULT 'N',
      `ca_iodef` text NOT NULL,
      `ca_critical` tinyint(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`),
      UNIQUE KEY (`ca_issue`)
    ) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
    
    ALTER TABLE `dns_ssl_ca` ADD UNIQUE(`ca_issue`);
    
    UPDATE `dns_ssl_ca` SET `ca_issue` = 'comodo.com' WHERE `ca_issue` = 'comodoca.com';
    DELETE FROM `dns_ssl_ca` WHERE `ca_issue` = 'geotrust.com';
    DELETE FROM `dns_ssl_ca` WHERE `ca_issue` = 'thawte.com';
    UPDATE `dns_ssl_ca` SET `ca_name` = 'Symantec / Thawte / GeoTrust' WHERE `ca_issue` = 'symantec.com';
    
    ALTER TABLE `dns_rr` CHANGE `type` `type` ENUM('A','AAAA','ALIAS','CAA','CNAME','DS','HINFO','LOC','MX','NAPTR','NS','PTR','RP','SRV','TXT','TLSA','DNSKEY') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
    ALTER TABLE `dns_rr` CHANGE `data` `data` TEXT NOT NULL;
    INSERT IGNORE INTO `dns_ssl_ca` (`id`, `sys_userid`, `sys_groupid`, `sys_perm_user`, `sys_perm_group`, `sys_perm_other`, `active`, `ca_name`, `ca_issue`, `ca_wildcard`, `ca_iodef`, `ca_critical`) VALUES
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'AC Camerfirma', 'camerfirma.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'ACCV', 'accv.es', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Actalis', 'actalis.it', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Amazon', 'amazon.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Asseco', 'certum.pl', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Buypass', 'buypass.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'CA Disig', 'disig.sk', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'CATCert', 'aoc.cat', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Certinomis', 'www.certinomis.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Certizen', 'hongkongpost.gov.hk', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'certSIGN', 'certsign.ro', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'CFCA', 'cfca.com.cn', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Chunghwa Telecom', 'cht.com.tw', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Comodo', 'comodoca.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'D-TRUST', 'd-trust.net', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'DigiCert', 'digicert.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'DocuSign', 'docusign.fr', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'e-tugra', 'e-tugra.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'EDICOM', 'edicomgroup.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Entrust', 'entrust.net', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Firmaprofesional', 'firmaprofesional.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'FNMT', 'fnmt.es', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'GlobalSign', 'globalsign.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'GoDaddy', 'godaddy.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Google Trust Services', 'pki.goog', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'GRCA', 'gca.nat.gov.tw', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'HARICA', 'harica.gr', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'IdenTrust', 'identrust.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Izenpe', 'izenpe.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Kamu SM', 'kamusm.gov.tr', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Let''s Encrypt', 'letsencrypt.org', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Microsec e-Szigno', 'e-szigno.hu', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'NetLock', 'netlock.hu', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'PKIoverheid', 'www.pkioverheid.nl', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'PROCERT', 'procert.net.ve', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'QuoVadis', 'quovadisglobal.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'SECOM', 'secomtrust.net', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Sertifitseerimiskeskuse', 'sk.ee', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'StartCom', 'startcomca.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'SwissSign', 'swisssign.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Symantec / Thawte / GeoTrust', 'symantec.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'T-Systems', 'telesec.de', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Telia', 'telia.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Trustwave', 'trustwave.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'Web.com', 'web.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'WISeKey', 'wisekey.com', 'Y', '', 0),
    (NULL, 1, 1, 'riud', 'riud', '', 'Y', 'WoSign', 'wosign.com', 'Y', '', 0);
    
    ALTER TABLE `dns_soa` CHANGE `xfer` `xfer` TEXT NULL;
    ALTER TABLE `dns_soa` CHANGE `also_notify` `also_notify` TEXT NULL;
    ALTER TABLE `dns_slave` CHANGE `xfer` `xfer` TEXT NULL;
    ALTER TABLE `firewall` CHANGE `tcp_port` `tcp_port` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
    ALTER TABLE `firewall` CHANGE `udp_port` `udp_port` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
     
  5. jolt

    jolt New Member

    mysql dbispconfig --force < upd_0087.sql
    ERROR 1060 (42S21) at line 1: Duplicate column name 'session_id'
    ERROR 1060 (42S21) at line 4: Duplicate column name 'php_fpm_chroot', so it must be there.

    Strange thing is, when I copy the query from the error message and run it locally in the db it does work. So I don't get where the fsck it is coming from!
     
  6. jolt

    jolt New Member

    This is while changing a subdomain pointer:

    (`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`,`ssl_letsencrypt_exclude`,`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`,`php_fpm_chroot`,`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`,`enable_spdy`,`last_quota_notification`,`rewrite_rules`,`added_date`,`added_by`,`directive_snippets_id`,`enable_pagespeed`,`http_port`,`https_port`,`log_retention`) VALUES ('95','1','22','riud','riud','','5',NULL,NULL,'domain.eu','alias','93',NULL,NULL,NULL,NULL,NULL,'0','-1','y','y','y','1','1','*','y','n','n','n','permanent','','*_to_domain_tld','n','n','n','n',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'awstats','All',NULL,NULL,'y','n','dynamic','10','2','1','5','10','0',NULL,NULL,'none','1',NULL,'y','n',NULL,NULL,'n',NULL,NULL,NULL,NULL,'0','n','80','443','10') -> 1054 (Unknown column 'php_fpm_chroot' in 'field list')


    looking at it it must be some type of linebreak in the php_open_basedir part, where does that come from? How is that list built?

    The query works great when I run it locally and remove the linebreak after php_open_basedir.
     
  7. till

    till Super Moderator Staff Member ISPConfig Developer

    Not sure if the linebreak is really in the sql query or if its just in the output that you see. The query is built from file /usr/local/ispconfig/interface/web/sites/form/web_vhost_domain.tform.php and there is no linebreak in there in the field definition in the released source code, but you might check it on your server directly. Line 859

    'php_open_basedir' => array (

    if there is a linebreak, then it must be within the ' ', e.g.:

    'php_open_basedir
    ' => array (
     
  8. jolt

    jolt New Member

    Seems like I managed to solve it! By looking at the server config for the slave server I saw an old reference to php5.3 in php_open_basedir config. I removed that and commited the changes in the UI and voilĂ , server now seems to be back to work as usual!
     

Share This Page