Cannot shutdown database due to a session that cannot be closed

Andrew_Bacon

As part of ongoing maintenance of our 3 node 6.1.0 Vertica database we had to shutdown the database. During this process we attempted to forcefully close all the sessions using the select close_all_sessions(); Unfortunately this did not close all the sessions. One remained and could not be closed directly with the close_session() command. We confirmed that this session was no longer running on the client host and therefore no session should have been present. We then tried the shutdown(‘true’) command to forcefully shutdown the database. This could not complete and we saw that the database log file was waiting continually for the session to close. Finally we had to forcefully kill the vertica process on the node which the session was connected to. Is there a bug fix or improved workaround for when sessions become detached in this way, as it is impossible to shutdown the database in a 'safe' fashion.

Valentyn_Vdovyc_1

Hi. I faced the same issue. Is there any workarounds? Thanks.

Daniel_Leybovic

Hi! I solved same issue it by killing all statements within session with "INTERRUPT_STATEMENT( 'session_id ', statement_id )" (https://my.vertica.com/docs/6.1.x/HTML/index.htm#9857.htm) After it I was able to close a problematic session.

Andrew_Bacon

Hi Valentyn, no, not to my knowledge, a forcefull kill of the vertica process is the only option I had. Vertica are aware of the problem but I don't have an internal Vertica bug ID for it yet. If it happens again then I'll be able to investigate further. If you can collect diagnostics and send to Vertica then that may help them diagnose the issue. Unfortunately when I ran diagnostics, the diagnostics killed the vertica process - which is the focus of another support case I have open. If you're running a recent version of Vertica, there's a new scrutinize script to replace diagnostics in /opt/vertica/bin, which appears to be quicker and safer to use.