Some SIP machines, automated materials handlers for example, connect to the SIP client and maintain a connection, sending SC status messages to keep the connection alive This works, but can lead to issues if the SIP connection outlasts the DB connection. To mitigate this, we should build a DB connection attempt into the SC status call, and terminate the SIP connection if we cannot reach the DB
Created attachment 120843 [details] [review] Bug 28320: Add DB connection check to the SIP SC status message This patch adds a lookup of the sip user during an SC status, confirming that our DB ocnnection is working, and that our user is still valid Additionally, it adds support for SC status to the sip_cli_emulator and adds basic test coverage for the SC status message To test: 1 - Apply patch 2 - Restart SP server 3 - perl misc/sip_cli_emulator.pl -a localhost -p 6001 -l CPL -m sc_status_request -su term1 -sp term1 4 - prove -v t/db_dependent/SIP/Message.t
Created attachment 120860 [details] [review] Bug 28320: Add DB connection check to the SIP SC status message This patch adds a lookup of the sip user during an SC status, confirming that our DB ocnnection is working, and that our user is still valid Additionally, it adds support for SC status to the sip_cli_emulator and adds basic test coverage for the SC status message To test: 1 - Apply patch 2 - Restart SP server 3 - perl misc/sip_cli_emulator.pl -a localhost -p 6001 -l CPL -m sc_status_request -su term1 -sp term1 4 - prove -v t/db_dependent/SIP/Message.t Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
What happens with that die()? Does that crash the server or just severs the connection?
Also, DBIx::Class should reconnect to the database, so I'm a bit confused by what's going on here...
I should have commented on the bug instead of asking Nick/Kyle directly.. I agree, the die feels weird and I'm surprised the db connection isn't retried.
(In reply to Martin Renvoize from comment #5) > I should have commented on the bug instead of asking Nick/Kyle directly.. I > agree, the die feels weird and I'm surprised the db connection isn't retried. (In reply to David Cook from comment #3) > What happens with that die()? Does that crash the server or just severs the > connection? The die simply kills the current connection, the server keeps running and can be reconnected to, to test: 1 - telnet localhost 6001 2 - input: 9300CNterm1|COterm1|CPCPL| 3 - Should get a 94 response 4 - Stop the DB in another terminal - docket stop koha_db_1 5 - input to telnet: 9900302.00 6 - "Connection closed by foreign host." 7 - Start the DB 8 - repeat 1-3 9 - input to telnet: 9900302.00 10 - You get a 98 response The DB connection should be retried, and generally is: 1 - telnet localhost 6001 2 - input: 9300CNterm1|COterm1|CPCPL| 3 - Should get a 94 response 4 - In another terminal: sudo koha-mysql kohadev 5 - SHOW FULL PROCESSLIST; 6 - You should see a connection that represents your SIP session above 7 - You can send a 99 SC status to reset the sleep/time so you can identify which one is your sip connection input to telnet: 9900302.00 8 - Once you have identified, in MySQL, kill the correct prcoess number: KILL 3 9 - Send the 99 again 10 - The DB reconnects and continues without interrupting the SIP session What we have seen is that for long standing SIP sessions, the DB won't reconnect gracefully. In these cases the SC statuses keep reporting connection, but the next checkin fails. After that failure the DB may reconnect and continue, but numbers of items can pass through the AMH/Self check without checking in. Pinging the DB connection eliminates the issue, as does a restart of the SIP server by forcing new connections. In the case where the SC status would fail/die, the machines reconnect to the SIP server, and we fail a status message rather than a checkin
Sounds like a difficult one to troubleshoot :(
Created attachment 120955 [details] [review] Bug 28320: Add DB connection check to the SIP SC status message This patch adds a lookup of the sip user during an SC status, confirming that our DB ocnnection is working, and that our user is still valid Additionally, it adds support for SC status to the sip_cli_emulator and adds basic test coverage for the SC status message To test: 1 - Apply patch 2 - Restart SP server 3 - perl misc/sip_cli_emulator.pl -a localhost -p 6001 -l CPL -m sc_status_request -su term1 -sp term1 4 - prove -v t/db_dependent/SIP/Message.t Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Tested and works as expected. I read into the die and agree it's how it should be done in this case.. nice to learn. Passing QA
Pushed to master for 21.05, thanks to everybody involved!
Pushed to 20.11.x for 20.11.06
Pushed to 20.05.x for 20.05.12
Not backported to oldoldstable (19.11.x). Feel free to ask if it's needed.