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
<[email protected]>
Sent: Monday, March 13, 2023 6:46 AM
To: [email protected]
<[email protected]>
Cc: Gustav Weiss <[email protected]>
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<http://100.64.0.1:4723> (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
<[email protected]<mailto:[email protected]>>:
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:186da894f3d4cff311]
Office: +33156696210
Akamai Technologies
145 Broadway
Cambridge, MA 02142
Connect with Us:
[cid:186da894f3d5b006a2]<https://community.akamai.com/>
[cid:186da894f3d692e333] <http://blogs.akamai.com/> [cid:186da894f3d7745b44]
<https://twitter.com/akamai> [cid:186da894f3d855d355]
<http://www.facebook.com/AkamaiTechnologies> [cid:186da894f3d9374b66]
<http://www.linkedin.com/company/akamai-technologies> [cid:186da894f3da18c377]
<http://www.youtube.com/user/akamaitechnologies?feature=results_main>
_______________________________________________
PacketFence-users mailing list
[email protected]<mailto:[email protected]>
https://lists.sourceforge.net/lists/listinfo/packetfence-users
_______________________________________________
PacketFence-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/packetfence-users
