(8.0.29)
drop table tab1;
create table tab1
( col1 numeric not null primary key
, col2 char(255)
)
;
drop procedure proc1;
delimiter //
create procedure proc1(in x int)
begin
declare i int;
set i = 1;
start transaction;
while i <= x do
insert into tab1 values(i, concat('hoge', i) );
set i = i + 1;
end while;
commit;
end
//
delimiter ;
call proc1(55552);
select * from tab1 limit 10;
select count(*) from tab1;
ls -lh /var/lib/mysql/test/tab1.ibd
-- キャッシュクリア
systemctl restart mysqld
-- ブロック破壊実行
dd if=/dev/urandom of=/var/lib/mysql/test/tab1.ibd bs=16384 count=1 seek=140 conv=notrunc
select count(*) from tab1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
ERROR:
Can't connect to the server
エラーログ確認
2022-08-19T22:17:25.177238Z 0 [ERROR] [MY-011904] [InnoDB] Space id and page number stored in the page read in are [page id: space=1604612563, page number=3587993964], should be [page id: space=641, page number=140]
2022-08-19T22:17:25.177324Z 0 [ERROR] [MY-011906] [InnoDB] Database page corruption on disk or a failed file read of page [page id: space=641, page number=140]. You may have to recover from a backup.
InnoDB: End of page dump
2022-08-19T22:17:25.800571Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: buf0lru.cc:2127:bpage->buf_fix_count == 0 thread 140552448808704
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
(19c)
https://blogs.oracle.com/otnjp/post/shibacho-024
select * from v$tablespace order by ts#;
drop tablespace tbs01 including contents and datafiles;
create tablespace tbs01 datafile '/u01/app/oracle/oradata/orcl/pdb1/tbs01.dbf' size 100M uniform size 1m;
drop table tab1 purge;
create table tab1
( col1 number not null
, col2 char(1000)
)
tablespace tbs01;
insert /*+ append */ into tab1 select level, 'hoge'||to_char(level) from dual connect by level <= 55552;
commit;
select * from tab1 fetch first 10 rows only;
select count(*) from tab1;
col segment_name for a8
col tablespace_name for a8
select segment_type, segment_name, tablespace_name, bytes/1024/1024
from user_segments
where tablespace_name = 'TBS01' ;
col owner for a8
col segment_name for a8
select owner,segment_name,extent_id,file_id,block_id,blocks
from dba_extents
where owner = 'TEST'
and segment_name = 'TAB1'
order by file_id,block_id ;
-- キャッシュクリア
alter system flush buffer_cache ;
-- ブロック破壊実行
dd if=/dev/urandom of=/u01/app/oracle/oradata/orcl/pdb1/tbs01.dbf bs=8192 count=1 seek=140 conv=notrunc
select count(*) from tab1;
行1でエラーが発生しました。:
ORA-01578: Oracleデータ・ブロックに障害が発生しました(ファイル番号13、ブロック番号140) ORA-01110: データファイル13:
'/u01/app/oracle/oradata/orcl/pdb1/tbs01.dbf'
export nls_date_format="yyyy/mm/dd hh24:mi:ss"
rman
CONNECT TARGET "sys@pdb1 AS SYSDBA";
validate check logical tablespace tbs01;
(14)
\pset pager 0
drop table tab1;
create table tab1
( col1 numeric not null
, col2 char(1000)
)
;
insert into tab1 select g, 'hoge'||g::varchar from generate_series(1,55552) g;
select * from tab1 limit 10;
select count(*) from tab1;
/usr/pgsql-14/bin/oid2name
/usr/pgsql-14/bin/oid2name -d test
ls -lh /var/lib/pgsql/14/data/base/221801/272255
-- キャッシュクリア
sudo systemctl restart postgresql-14
-- ブロック破壊実行
dd if=/dev/urandom of=/var/lib/pgsql/14/data/base/221801/272255 bs=8192 count=1 seek=140 conv=notrunc
select count(*) from tab1;
ERROR: invalid page in block 140 of relation base/221801/272255
(2019)
OS: Ubuntu20.04
select * from sys.filegroups;
go
select * from sys.database_files;
go
alter database test remove file tbs01;
go
alter database test remove filegroup tbs01;
go
alter database test add filegroup tbs01;
go
alter database test add file
(name = tbs01 ,filename = '/var/opt/mssql/data/tbs01.ndf'
) to filegroup tbs01
;
go
drop table tab1;
go
create table tab1
( col1 numeric(38) not null primary key
, col2 char(1000)
) on tbs01
;
go
set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 55552
begin
insert into tab1 values(@i,'hoge'+cast(@i as char) );
set @i = @i + 1;
end
commit;
go
select top(10) * from tab1;
go
select count(*) from tab1;
go
select top(10) * from dbo.tab1
cross apply sys.fn_PhysLocCracker(%%physloc%%) as fplc
order by
fplc.file_id,
fplc.page_id,
fplc.slot_id
;
go
-- 復旧モード変更
※デフォルトのfullでは、エラーを再現できなかったため、simpleに変更
select name, recovery_model_desc
from sys.databases
where name = 'test' ;
go
use master;
go
alter database test set recovery simple ;
go
use test;
go
-- キャッシュクリア
checkpoint;
go
dbcc dropcleanbuffers;
go
-- ブロック破壊実行
sudo dd if=/dev/urandom of=/var/opt/mssql/data/tbs01.ndf bs=8192 count=1 seek=140 conv=notrunc
select count(*) from tab1;
go
Msg 824, Level 24, State 2, Server mmm182, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xdda188ae; actual: 0x848b6f8b). It occurred during a read of page (3:140) in database ID 5 at offset 0x00000000118000 in file '/var/opt/mssql/data/tbs01.ndf'. Additional messages in the SQL Server error log or operating system error log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
dbcc checkdb(test);
go
