Hi David, Please have a look at the pf-schema in https://github.com/inverse-inc/packetfence/tree/devel/db
Regards, Darren Satkunas Sr. Software Engineer [signature_756936796] Office: +1.617.444.1234 Cell: +1.617.444.1234 Akamai Technologies 145 Broadway Cambridge, MA 02142 Connect with Us: [signature_3704952916]<https://community.akamai.com/> [signature_52273732] <http://blogs.akamai.com/> [signature_3423262600] <https://twitter.com/akamai> [signature_3863475295] <http://www.facebook.com/AkamaiTechnologies> [signature_658561734] <http://www.linkedin.com/company/akamai-technologies> [signature_1988127824] <http://www.youtube.com/user/akamaitechnologies?feature=results_main> From: David Moore via PacketFence-users <packetfence-users@lists.sourceforge.net> Reply-To: "packetfence-users@lists.sourceforge.net" <packetfence-users@lists.sourceforge.net> Date: Monday, March 13, 2023 at 8:50 AM To: "packetfence-users@lists.sourceforge.net" <packetfence-users@lists.sourceforge.net> Cc: David Moore <dave.mo...@outlook.com> Subject: Re: [PacketFence-users] Nodes not displaying after upgrade to 12.2 Hello I noticed that it was an issue with the database so I added the missing table and also added 3 missing fields. I don't currently have any errors but would still like to know more about the database structure for packetfence. Does anyone know if there is documentation that lists all the table and field names, along with the field types? ________________________________ From: Gustav Weiss via PacketFence-users <packetfence-users@lists.sourceforge.net> Sent: Monday, March 13, 2023 6:46 AM To: packetfence-users@lists.sourceforge.net <packetfence-users@lists.sourceforge.net> Cc: Gustav Weiss <wgusta...@gmail.com> Subject: Re: [PacketFence-users] Nodes not displaying after upgrade to 12.2 Hello I get the same error in the packetfence.log I see that: root@PF01:~# tail -f /usr/local/pf/logs/packetfence.log Mar 13 11:17:17 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(13) INFO: [mac:[undef]] All cluster members are running the same configuration version (pf::pfcron::task::cluster_check::run) Mar 13 11:17:44 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(16) INFO: [mac:[undef]] Found a realm source local for user admin in realm null. (pf::authentication::adminAuthentication) Mar 13 11:17:44 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(16) INFO: [mac:[undef]] Authentication successful for admin in source local (SQL) (pf::authentication::authenticate) Mar 13 11:17:44 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(16) INFO: [mac:[undef]] Using sources local for matching (pf::authentication::match2) Mar 13 11:17:46 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(12) ERROR: [mac:[undef]] Database query failed with non retryable error: Table 'pf.node_current_session' doesn't exist (errno: 1146) [SELECT `node`.`status`, CASE IFNULL( (SELECT is_online from node_current_session as ncs WHERE ncs.mac = node.mac), 'unknown') WHEN 'unknown' THEN 'unknown' WHEN 0 THEN 'off' ELSE 'on' END AS `online`, `node`.`mac`, `node`.`computername`, `node`.`pid`, `ip4log`.`ip` AS `ip4log.ip`, `node`.`device_class`, `node`.`category_id`, `node`.`bypass_role_id` FROM node LEFT OUTER JOIN ip4log ON ( `ip4log`.`ip` = ( SELECT `ip` FROM `ip4log` WHERE `mac` = `node`.`mac` ORDER BY `start_time` DESC LIMIT 1 ) ) ORDER BY `online` ASC LIMIT ? OFFSET ?]{501, 0} (pf::dal::db_execute) Mar 13 11:17:47 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(16) INFO: [mac:[undef]] Database /usr/local/fingerbank/db/fingerbank_Upstream.db was changed or handles weren't initialized. Creating handle. (fingerbank::DB::SQLite::build_handle) Mar 13 11:18:16 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(12) INFO: [mac:[undef]] Using 300 resolution threshold (pf::pfcron::task::cluster_check::run) Mar 13 11:18:16 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(12) INFO: [mac:[undef]] All cluster members are running the same configuration version (pf::pfcron::task::cluster_check::run) Mar 13 11:18:17 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(14) INFO: [mac:[undef]] getting security_events triggers for accounting cleanup (pf::accounting::acct_maintenance) Mar 13 11:18:17 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(12) INFO: [mac:[undef]] processed 0 security_events during security_event maintenance (1678702697.14255 1678702697.1516) (pf::security_event::security_event_maintenance) Mar 13 11:19:17 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(14) INFO: [mac:[undef]] getting security_events triggers for accounting cleanup (pf::accounting::acct_maintenance) Mar 13 11:19:17 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(16) INFO: [mac:[undef]] Using 300 resolution threshold (pf::pfcron::task::cluster_check::run) Mar 13 11:19:17 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(16) INFO: [mac:[undef]] All cluster members are running the same configuration version (pf::pfcron::task::cluster_check::run) Mar 13 11:19:17 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(11) INFO: [mac:[undef]] processed 0 security_events during security_event maintenance (1678702757.11626 1678702757.18377) (pf::security_event::security_event_maintenance) Mar 13 11:19:22 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(13) ERROR: [mac:[undef]] Database query failed with non retryable error: Table 'pf.node_current_session' doesn't exist (errno: 1146) [SELECT `node`.`status`, CASE IFNULL( (SELECT is_online from node_current_session as ncs WHERE ncs.mac = node.mac), 'unknown') WHEN 'unknown' THEN 'unknown' WHEN 0 THEN 'off' ELSE 'on' END AS `online`, `node`.`mac`, `node`.`computername`, `node`.`pid`, `ip4log`.`ip` AS `ip4log.ip`, `node`.`device_class`, `node`.`category_id`, `node`.`bypass_role_id` FROM node LEFT OUTER JOIN ip4log ON ( `ip4log`.`ip` = ( SELECT `ip` FROM `ip4log` WHERE `mac` = `node`.`mac` ORDER BY `start_time` DESC LIMIT 1 ) ) ORDER BY `online` ASC LIMIT ? OFFSET ?]{501, 0} (pf::dal::db_execute) Mar 13 11:20:16 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(17) INFO: [mac:[undef]] Using 300 resolution threshold (pf::pfcron::task::cluster_check::run) Mar 13 11:20:16 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(17) INFO: [mac:[undef]] All cluster members are running the same configuration version (pf::pfcron::task::cluster_check::run) Mar 13 11:20:16 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(11) INFO: [mac:[undef]] processed 0 security_events during security_event maintenance (1678702816.13513 1678702816.17937) (pf::security_event::security_event_maintenance) Mar 13 11:20:17 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(10) INFO: [mac:[undef]] getting security_events triggers for accounting cleanup (pf::accounting::acct_maintenance) Mar 13 11:21:16 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(14) INFO: [mac:[undef]] processed 0 security_events during security_event maintenance (1678702876.27185 1678702876.28077) (pf::security_event::security_event_maintenance) Mar 13 11:21:16 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(16) INFO: [mac:[undef]] getting security_events triggers for accounting cleanup (pf::accounting::acct_maintenance) Mar 13 11:21:17 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(16) INFO: [mac:[undef]] Using 300 resolution threshold (pf::pfcron::task::cluster_check::run) Mar 13 11:21:17 PF01 pfperl-api-docker-wrapper[1723]: pfperl-api(16) INFO: [mac:[undef]] All cluster members are running the same configuration version (pf::pfcron::task::cluster_check::run) root@PF01:~# tail -f /usr/local/pf/logs/packetfence.log Mar 13 11:26:15 PF01 pfqueue[58896]: pfqueue(58896) ERROR: [mac:80:ce:62:9e:56:aa] Database query failed with non retryable error: Unknown column 'bypass_acls' in 'field list' (errno: 1054) [INSERT INTO `node` ( `autoreg`, `bandwidth_balance`, `bypass_acls`, `bypass_role_id`, `bypass_vlan`, `category_id`, `computername`, `detect_date`, `device_class`, `device_manufacturer`, `device_score`, `device_type`, `device_version`, `dhcp6_enterprise`, `dhcp6_fingerprint`, `dhcp_fingerprint`, `dhcp_vendor`, `last_arp`, `last_dhcp`, `last_seen`, `lastskip`, `mac`, `machine_account`, `notes`, `pid`, `regdate`, `sessionid`, `status`, `time_balance`, `unregdate`, `user_agent`, `voip`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ON DUPLICATE KEY UPDATE `autoreg` = ?, `bandwidth_balance` = ?, `bypass_acls` = ?, `bypass_role_id` = ?, `bypass_vlan` = ?, `category_id` = ?, `computername` = ?, `detect_date` = ?, `device_class` = ?, `device_manufacturer` = ?, `device_score` = ?, `device_type` = ?, `device_version` = ?, `dhcp6_enterprise` = ?, `dhcp6_fingerprint` = ?, `dhcp_fingerprint` = ?, `dhcp_vendor` = ?, `last_arp` = ?, `last_dhcp` = ?, `last_seen` = ?, `lastskip` = ?, `mac` = ?, `machine_account` = ?, `notes` = ?, `pid` = ?, `regdate` = ?, `sessionid` = ?, `status` = ?, `time_balance` = ?, `unregdate` = ?, `user_agent` = ?, `voip` = ?]{no, NULL, NULL, NULL, NULL, NULL, NB06, 2023-03-13 11:26:15, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1,3,6,15,31,33,43,44,46,47,119,121,249,252, MSFT 5.0, 0000-00-00 00:00:00, 2023-03-13 11:26:15, 0000-00-00 00:00:00, 0000-00-00 00:00:00, 80:ce:62:9e:56:aa, NULL, NULL, default, 0000-00-00 00:00:00, NULL, unreg, NULL, 0000-00-00 00:00:00, NULL, no, no, NULL, NULL, NULL, NULL, NULL, NB06, 2023-03-13 11:26:15, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1,3,6,15,31,33,43,44,46,47,119,121,249,252, MSFT 5.0, 0000-00-00 00:00:00, 2023-03-13 11:26:15, 0000-00-00 00:00:00, 0000-00-00 00:00:00, 80:ce:62:9e:56:aa, NULL, NULL, default, 0000-00-00 00:00:00, NULL, unreg, NULL, 0000-00-00 00:00:00, NULL, no} (pf::dal::db_execute) Mar 13 11:26:15 PF01 pfqueue[58896]: pfqueue(58896) ERROR: [mac:unknown] Database query failed with non retryable error: Unknown column 'node.bypass_acls' in 'field list' (errno: 1054) [SELECT `node`.`mac` AS `mac`, `node`.`pid` AS `pid`, `node`.`category_id` AS `category_id`, `node`.`detect_date` AS `detect_date`, `node`.`regdate` AS `regdate`, `node`.`unregdate` AS `unregdate`, `node`.`lastskip` AS `lastskip`, `node`.`time_balance` AS `time_balance`, `node`.`bandwidth_balance` AS `bandwidth_balance`, `node`.`status` AS `status`, `node`.`user_agent` AS `user_agent`, `node`.`computername` AS `computername`, `node`.`notes` AS `notes`, `node`.`last_arp` AS `last_arp`, `node`.`last_dhcp` AS `last_dhcp`, `node`.`dhcp_fingerprint` AS `dhcp_fingerprint`, `node`.`dhcp6_fingerprint` AS `dhcp6_fingerprint`, `node`.`dhcp_vendor` AS `dhcp_vendor`, `node`.`dhcp6_enterprise` AS `dhcp6_enterprise`, `node`.`device_type` AS `device_type`, `node`.`device_class` AS `device_class`, `node`.`device_version` AS `device_version`, `node`.`device_score` AS `device_score`, `node`.`device_manufacturer` AS `device_manufacturer`, `node`.`bypass_vlan` AS `bypass_vlan`, `node`.`voip` AS `voip`, `node`.`autoreg` AS `autoreg`, `node`.`sessionid` AS `sessionid`, `node`.`machine_account` AS `machine_account`, `node`.`bypass_role_id` AS `bypass_role_id`, `node`.`last_seen` AS `last_seen`, `node`.`bypass_acls` AS `bypass_acls`, `nc`.`name` AS `category`, `nr`.`name` AS `bypass_role` FROM node LEFT OUTER JOIN `node_category` AS `nc` ON ( `node`.`category_id` = `nc`.`category_id` ) LEFT OUTER JOIN `node_category` AS `nr` ON ( `node`.`bypass_role_id` = `nr`.`category_id` ) WHERE ( `node`.`mac` = ? )]{80:ce:62:9e:56:aa} (pf::dal::db_execute) Mar 13 11:26:15 PF01 pfqueue[58896]: pfqueue(58896) ERROR: [mac:unknown] Error while communicating with the Fingerbank collector. 500 Can't connect to 100.64.0.1:4723<https://urldefense.com/v3/__http:/100.64.0.1:4723__;!!GjvTz_vk!Si9Y7uwmZloPyMgjp6XH-MWD2Puo2AedfW0VAVkT9Rw7D3VCJA0h2Ng7O6CTJs9z2WGyb7r4eNavCFrWcfnjDowbaGSH3IbB2Ck$> (Connection refused) (pf::fingerbank::endpoint_attributes) Mar 13 11:26:15 PF01 pfqueue[58896]: pfqueue(58896) ERROR: [mac:unknown] Unable to fetch query arguments for Fingerbank query. Aborting. (pf::fingerbank::process) Mar 13 11:27:15 PF01 pfperl-api-docker-wrapper[58641]: pfperl-api(19) ERROR: [mac:[undef]] Database query failed with non retryable error: Table 'pf.node_current_session' doesn't exist (errno: 1146) [SELECT `node`.`status`, CASE IFNULL( (SELECT is_online from node_current_session as ncs WHERE ncs.mac = node.mac), 'unknown') WHEN 'unknown' THEN 'unknown' WHEN 0 THEN 'off' ELSE 'on' END AS `online`, `node`.`mac`, `node`.`computername`, `node`.`pid`, `ip4log`.`ip` AS `ip4log.ip`, `node`.`device_class`, `node`.`category_id`, `node`.`bypass_role_id` FROM node LEFT OUTER JOIN ip4log ON ( `ip4log`.`ip` = ( SELECT `ip` FROM `ip4log` WHERE `mac` = `node`.`mac` ORDER BY `start_time` DESC LIMIT 1 ) ) ORDER BY `online` ASC LIMIT ? OFFSET ?]{501, 0} (pf::dal::db_execute) Mar 13 11:27:16 PF01 pfperl-api-docker-wrapper[58641]: pfperl-api(14) INFO: [mac:[undef]] Database /usr/local/fingerbank/db/fingerbank_Upstream.db was changed or handles weren't initialized. Creating handle. (fingerbank::DB::SQLite::build_handle) Mar 13 11:27:17 PF01 pfperl-api-docker-wrapper[58641]: pfperl-api(19) ERROR: [mac:[undef]] Database query failed with non retryable error: Table 'pf.node_current_session' doesn't exist (errno: 1146) [SELECT `node`.`status`, CASE IFNULL( (SELECT is_online from node_current_session as ncs WHERE ncs.mac = node.mac), 'unknown') WHEN 'unknown' THEN 'unknown' WHEN 0 THEN 'off' ELSE 'on' END AS `online`, `node`.`mac`, `node`.`computername`, `node`.`pid`, `ip4log`.`ip` AS `ip4log.ip`, `node`.`device_class`, `node`.`category_id`, `node`.`bypass_role_id` FROM node LEFT OUTER JOIN ip4log ON ( `ip4log`.`ip` = ( SELECT `ip` FROM `ip4log` WHERE `mac` = `node`.`mac` ORDER BY `start_time` DESC LIMIT 1 ) ) WHERE ( ( (NOT EXISTS (SELECT 1 from node_current_session as ncs WHERE ncs.mac = node.mac)) OR (EXISTS (SELECT 1 from node_current_session as ncs WHERE ncs.mac = node.mac AND NOT is_online)) ) ) ORDER BY `node`.`last_seen` DESC LIMIT ? OFFSET ?]{501, 0} (pf::dal::db_execute) Mar 13 11:27:18 PF01 pfperl-api-docker-wrapper[58641]: pfperl-api(19) ERROR: [mac:[undef]] Database query failed with non retryable error: Table 'pf.node_current_session' doesn't exist (errno: 1146) [SELECT `node`.`status`, CASE IFNULL( (SELECT is_online from node_current_session as ncs WHERE ncs.mac = node.mac), 'unknown') WHEN 'unknown' THEN 'unknown' WHEN 0 THEN 'off' ELSE 'on' END AS `online`, `node`.`mac`, `node`.`computername`, `node`.`pid`, `ip4log`.`ip` AS `ip4log.ip`, `node`.`device_class`, `node`.`category_id`, `node`.`bypass_role_id` FROM node LEFT OUTER JOIN ip4log ON ( `ip4log`.`ip` = ( SELECT `ip` FROM `ip4log` WHERE `mac` = `node`.`mac` ORDER BY `start_time` DESC LIMIT 1 ) ) WHERE ( EXISTS (SELECT 1 from node_current_session as ncs WHERE ncs.mac = node.mac AND is_online) ) ORDER BY `node`.`last_seen` DESC LIMIT ? OFFSET ?]{501, 0} (pf::dal::db_execute) Mar 13 11:27:20 PF01 pfperl-api-docker-wrapper[58641]: pfperl-api(19) ERROR: [mac:[undef]] Database query failed with non retryable error: Table 'pf.node_current_session' doesn't exist (errno: 1146) [SELECT `node`.`status`, CASE IFNULL( (SELECT is_online from node_current_session as ncs WHERE ncs.mac = node.mac), 'unknown') WHEN 'unknown' THEN 'unknown' WHEN 0 THEN 'off' ELSE 'on' END AS `online`, `node`.`mac`, `node`.`computername`, `node`.`pid`, `ip4log`.`ip` AS `ip4log.ip`, `node`.`device_class`, `node`.`category_id`, `node`.`bypass_role_id` FROM node LEFT OUTER JOIN ip4log ON ( `ip4log`.`ip` = ( SELECT `ip` FROM `ip4log` WHERE `mac` = `node`.`mac` ORDER BY `start_time` DESC LIMIT 1 ) ) LEFT OUTER JOIN locationlog ON ( ( `locationlog`.`end_time` = ? AND `locationlog`.`mac` = `node`.`mac` ) ) WHERE ( `locationlog`.`switch_ip_int` = ? ) ORDER BY `node`.`last_seen` DESC LIMIT ? OFFSET ?]{0000-00-00 00:00:00, 184289994, 501, 0} (pf::dal::db_execute) Mar 13 11:27:21 PF01 pfperl-api-docker-wrapper[58641]: pfperl-api(19) ERROR: [mac:[undef]] Database query failed with non retryable error: Table 'pf.node_current_session' doesn't exist (errno: 1146) [SELECT `node`.`status`, CASE IFNULL( (SELECT is_online from node_current_session as ncs WHERE ncs.mac = node.mac), 'unknown') WHEN 'unknown' THEN 'unknown' WHEN 0 THEN 'off' ELSE 'on' END AS `online`, `node`.`mac`, `node`.`computername`, `node`.`pid`, `ip4log`.`ip` AS `ip4log.ip`, `node`.`device_class`, `node`.`category_id`, `node`.`bypass_role_id` FROM node LEFT OUTER JOIN ip4log ON ( `ip4log`.`ip` = ( SELECT `ip` FROM `ip4log` WHERE `mac` = `node`.`mac` ORDER BY `start_time` DESC LIMIT 1 ) ) LEFT OUTER JOIN locationlog ON ( ( `locationlog`.`end_time` = ? AND `locationlog`.`mac` = `node`.`mac` ) ) WHERE ( `locationlog`.`switch_ip_int` = ? ) ORDER BY `node`.`last_seen` DESC LIMIT ? OFFSET ?]{0000-00-00 00:00:00, 184290042, 501, 0} (pf::dal::db_execute) It seems that a database table is missing after the update. Now I had to restore the PF server. I also upgraded from 12.1 but we installed pf from the ISO. Greetings, Gustav Am Fr., 10. März 2023 um 13:44 Uhr schrieb Quiniou-Briand, Nicolas via PacketFence-users <packetfence-users@lists.sourceforge.net<mailto:packetfence-users@lists.sourceforge.net>>: Hello David, Could you send us what you see in Web browser console for nodes/search API call ? Thanks. Nicolas Quiniou-Briand Product Support Engineer [cid:image001.png@01D95590.2D96F620] Office: +33156696210 Akamai Technologies 145 Broadway Cambridge, MA 02142 Connect with Us: [cid:186da894f3d5b006a2]<https://community.akamai.com/> [cid:image009.png@01D95590.2D96F620] <http://blogs.akamai.com/> [cid:image010.png@01D95590.2D96F620] <https://urldefense.com/v3/__https:/twitter.com/akamai__;!!GjvTz_vk!Si9Y7uwmZloPyMgjp6XH-MWD2Puo2AedfW0VAVkT9Rw7D3VCJA0h2Ng7O6CTJs9z2WGyb7r4eNavCFrWcfnjDowbaGSH4F5V5fY$> [cid:image011.png@01D95590.2D96F620] <https://urldefense.com/v3/__http:/www.facebook.com/AkamaiTechnologies__;!!GjvTz_vk!Si9Y7uwmZloPyMgjp6XH-MWD2Puo2AedfW0VAVkT9Rw7D3VCJA0h2Ng7O6CTJs9z2WGyb7r4eNavCFrWcfnjDowbaGSHSideDT4$> [cid:image012.png@01D95590.2D96F620] <https://urldefense.com/v3/__http:/www.linkedin.com/company/akamai-technologies__;!!GjvTz_vk!Si9Y7uwmZloPyMgjp6XH-MWD2Puo2AedfW0VAVkT9Rw7D3VCJA0h2Ng7O6CTJs9z2WGyb7r4eNavCFrWcfnjDowbaGSHmAAcNVo$> [cid:image013.png@01D95590.2D96F620] <https://urldefense.com/v3/__http:/www.youtube.com/user/akamaitechnologies?feature=results_main__;!!GjvTz_vk!Si9Y7uwmZloPyMgjp6XH-MWD2Puo2AedfW0VAVkT9Rw7D3VCJA0h2Ng7O6CTJs9z2WGyb7r4eNavCFrWcfnjDowbaGSHg_Qcu6k$> _______________________________________________ PacketFence-users mailing list PacketFence-users@lists.sourceforge.net<mailto:PacketFence-users@lists.sourceforge.net> https://lists.sourceforge.net/lists/listinfo/packetfence-users<https://urldefense.com/v3/__https:/lists.sourceforge.net/lists/listinfo/packetfence-users__;!!GjvTz_vk!Si9Y7uwmZloPyMgjp6XH-MWD2Puo2AedfW0VAVkT9Rw7D3VCJA0h2Ng7O6CTJs9z2WGyb7r4eNavCFrWcfnjDowbaGSH7CXt_Sc$>
_______________________________________________ PacketFence-users mailing list PacketFence-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/packetfence-users
