強制終了

(5.6)

※データ更新しながら
強制終了し、通常終了の場合とログを比較する


drop table tab1;
create table tab1(col1 int);

drop procedure proc1;
delimiter //
create procedure proc1()
begin
while true do
insert into tab1 values(1);
update tab1 set col1 = col1 + 1;
end while;
end
//
delimiter ;

start transaction;

call proc1();

 


【1】通常終了の場合
systemctl stop mysqld
systemctl start mysqld

--更新中のセッション
ERROR 1317 (70100): Query execution was interrupted

--終了時ログ抜粋
[Note] /usr/sbin/mysqld: Normal shutdown
[Note] Giving 1 client threads a chance to die gracefully
[Note] /usr/sbin/mysqld: Shutdown complete


--起動時ログ抜粋
mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[Note] /usr/sbin/mysqld: ready for connections.

【2】強制終了の場合

kill -9 `ps -ef | grep mysql | grep "/usr/bin/mysqld_safe" | grep -v grep | awk '{print $2}'`

※killしても自動で起動する

--更新中のセッション
ERROR 1317 (70100): Query execution was interrupted

--終了時ログ抜粋
[Note] /usr/sbin/mysqld: Normal shutdown
[Note] Giving 1 client threads a chance to die gracefully
[Note] /usr/sbin/mysqld: Shutdown complete


--起動時ログ抜粋
mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[Note] /usr/sbin/mysqld: ready for connections.


※正常終了の場合と比べて相違なし

 

 

 

(12cR1)

※データ更新しながら
強制終了し、通常終了の場合とログを比較する


drop table tab1 purge;
create table tab1(col1 int);

declare
begin
while true loop
insert into tab1 values(1);
update tab1 set col1 = col1 + 1;
end loop;
end;
/

 

【1】通常終了の場合

shutdown immediate
startup

--更新中のセッション

行1でエラーが発生しました。:
ORA-01089: 即時シャットダウンまたはクローズ処理中 - 操作はできません ORA-06512: 行5
プロセスID:
10933
セッションID: 49、シリアル番号: 47928

--終了時ログ抜粋
Shutting down instance (immediate)
Successful close of redo thread 1
Instance shutdown complete

--起動時ログ抜粋
Starting ORACLE instance (normal) (OS id: 11336)

 


【2】強制終了の場合

shutdown abort
startup


--更新中のセッション

行1でエラーが発生しました。:
ORA-03113: 通信チャネルでend-of-fileが検出されました プロセスID:
11743
セッションID: 41、シリアル番号: 53434

 

--終了時ログ抜粋
Shutting down instance (abort)
Instance shutdown complete

--起動時ログ抜粋
Starting ORACLE instance (normal) (OS id: 11771)
Beginning crash recovery of 1 threads

 

(9.4)

※データ更新しながら
強制終了し、通常終了の場合とログを比較する


drop table tab1;
create table tab1(col1 int);


do
$$
declare
begin
while true loop
insert into tab1 values(1);
update tab1 set col1 = col1 + 1;
end loop;
end
$$
;


【1】通常終了の場合


/usr/pgsql-9.4/bin/pg_ctl stop -D ${PGDATA} -m fast
/usr/pgsql-9.4/bin/pg_ctl start -D ${PGDATA} -w

--更新中のセッション

FATAL: terminating connection due to administrator command
CONTEXT: SQL statement "update tab1 set col1 = col1 + 1"
PL/pgSQL function inline_code_block line 6 at SQL statement
サーバとの接続が想定外にクローズされました
おそらく要求の処理前または処理中にサーバが異常終了
したことを意味しています。
サーバーへの接続が切れました。リセットしています: 失敗。


--終了時ログ抜粋
LOG: shutting down
FATAL: the database system is shutting down
LOG: database system is shut down

--起動時ログ抜粋

LOG: database system was shut down at 2020-03-20 13:48:18 JST
LOG: database system is ready to accept connections


【2】強制終了の場合

/usr/pgsql-9.4/bin/pg_ctl stop -D ${PGDATA} -m immediate
/usr/pgsql-9.4/bin/pg_ctl start -D ${PGDATA} -w

--更新中のセッション

WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
CONTEXT: SQL statement "update tab1 set col1 = col1 + 1"
PL/pgSQL function inline_code_block line 6 at SQL statement
サーバとの接続が想定外にクローズされました
おそらく要求の処理前または処理中にサーバが異常終了
したことを意味しています。
サーバーへの接続が切れました。リセットしています: 失敗。

--終了時ログ抜粋

LOG: received immediate shutdown request
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
CONTEXT: SQL statement "update tab1 set col1 = col1 + 1"
PL/pgSQL function inline_code_block line 6 at SQL statement

--起動時ログ抜粋

LOG: database system was interrupted; last known up at 2020-03-20 13:49:06 JST
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 19/6F2A1CB8
FATAL: the database system is starting up
LOG: redo done at 19/737A9FC0
LOG: last completed transaction was at log time 2020-03-20 13:49:58.974285+09
LOG: database system is ready to accept connections

 

(2014)


※データ更新しながら
強制終了し、通常終了の場合とログを比較する


drop table tab1;
create table tab1(col1 int);

begin transaction;

while 1=1
begin
insert into tab1 values(1);
update tab1 set col1 = col1 + 1;
end


【1】通常終了の場合
net stop "SQL Server (MSSQLSERVER)"
net start "SQL Server (MSSQLSERVER)"


--更新中のセッション
サーバーから結果を受信しているときに、トランスポート レベルのエラーが発生しました。 (provider: Shared Memory Provider, error: 0 - パイプは終了しました。)

--終了時ログ抜粋

SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
Service Broker manager has shut down.
.NET Framework runtime has been stopped.
SQL Server shutdown has been initiated
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

 

--起動時ログ抜粋
Starting up database 'master'.
Recovery of database 'test' (7) is 0% complete (approximately 2215 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
Recovery of database 'test' (7) is 0% complete (approximately 2201 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'test' (7) is 0% complete (approximately 2201 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
1 transactions rolled back in database 'test' (7:0). This is an informational message only. No user action is required.
Recovery is writing a checkpoint in database 'test' (7). This is an informational message only. No user action is required.
Recovery completed for database test (database ID 7) in 10 second(s) (analysis 38 ms, redo 5395 ms, undo 4641 ms.) This is an informational message only. No user action is required.
Recovery is complete. This is an informational message only. No user action is required.

 

【2】強制終了の場合

taskkill /F /IM sqlservr.exe

net start "SQL Server (MSSQLSERVER)"


--更新中のセッション
サーバーから結果を受信しているときに、トランスポート レベルのエラーが発生しました。 (provider: Shared Memory Provider, error: 0 - パイプは終了しました。)


--終了時ログ抜粋

ログなし


--起動時ログ抜粋

Starting up database 'master'.
Recovery of database 'test' (7) is 0% complete (approximately 2755 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
Recovery of database 'test' (7) is 0% complete (approximately 2295 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'test' (7) is 0% complete (approximately 1740 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
1 transactions rolled back in database 'test' (7:0). This is an informational message only. No user action is required.
Recovery is writing a checkpoint in database 'test' (7). This is an informational message only. No user action is required.
Recovery completed for database test (database ID 7) in 10 second(s) (analysis 2414 ms, redo 4275 ms, undo 3191 ms.) This is an informational message only. No user action is required.
Recovery is complete. This is an informational message only. No user action is required.


※正常終了時にも起動時にリカバリを実施している模様