To była długa bezsenna noc. Szczekające psy sąsiada, deszcz bijący o szyby pokoju.
Czwarta nad ranem, gdy w końcu udało się zasnąć, komórka służbowa zaczęła dzwonić.
Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.
Po raz kolejny przepełnił się autonumer jednej z licznych tabel na serwerze produkcyjnym.
Jak się przed tym obronić ? Jak oszacować kiedy skończy nam się możliwość generowania autonumeracji ?
Czy da się opracować proaktywny mechanizm wspomagający codziennie utrzymanie systemów ?
Spróbujmy oszacować to jedynie na podstawie metadanych.
Co mamy do dyspozycji ?
Liczbę rekordów w tabeli, jej datę utworzenia, aktualną wartość licznika, maksymalną wartość licznika.
Zastosowany jest najprostszy z możliwych. algorytmów.
Zakładamy, że liczba rekordów przyrasta liniowo w czasie, parametry tego wzrostu są tak dopasowane, że w chwili t0 (dla daty utworzenia) liczba rekordów wynosi 0, a dla chwili tk (dla daty katastrofy, czyli przepełnienia typu całkowitego) liczba rekordów jest równa maksymalnej.
Poniżej umieszczono szkic skryptu:
SET tran isolation level READ uncommitted
IF object_id('tempdb..#DataTypeMaxValue') IS NULL
BEGIN
CREATE TABLE #DataTypeMaxValue (DataType VARCHAR(50), MaxValue BIGINT)
END
IF object_id('tempdb..#report') IS NOT NULL
BEGIN
DROP TABLE #report
END
INSERT INTO #DataTypeMaxValue VALUES
('tinyint' , 255),
('smallint' , 32767),
('int' , 2147483647),
('bigint' , 9223372036854775807)
SELECT
DISTINCT OBJECT_NAME (IC.object_id) AS TableName,
CONVERT(VARCHAR(10),TN.create_date,120) AS CreateDate,
DATEDIFF(DAY,CONVERT(VARCHAR(10),TN.create_date,120),getdate()) AS NumberOfDaysFromCreation,
IC.name AS ColumnName,
TYPE_NAME(IC.system_type_id) AS ColumnDataType,
DTM.MaxValue AS MaxDataTypeValue,
IC.seed_value IdentitySeed,
IC.increment_value AS IdentityIncrement,
IC.last_value,
sum(DBPS.row_count) AS NumberOfRows,
abs((try_convert(DECIMAL(9,2),try_CONVERT(BIGINT,IC.last_value)*100/DTM.MaxValue))) AS ReachMaxValuePercent ,
CONVERT(datetime,NULL) AS DisasterDay
INTO #report
FROM sys.identity_columns IC
JOIN sys.tables TN ON IC.object_id = TN.object_id
JOIN #DataTypeMaxValue DTM ON TYPE_NAME(IC.system_type_id)=DTM.DataType
JOIN sys.dm_db_partition_stats DBPS ON DBPS.object_id =IC.object_id
WHERE DBPS.row_count >=1000
AND DBPS.partition_number IN (0,1)
AND DBPS.index_id<2
group BY
OBJECT_NAME (IC.object_id) ,
CONVERT(VARCHAR(10),TN.create_date,120) ,
DATEDIFF(DAY,CONVERT(VARCHAR(10),TN.create_date,120),getdate()),
IC.name,
TYPE_NAME(IC.system_type_id) ,
DTM.MaxValue ,
IC.seed_value ,
IC.increment_value,
IC.last_value,
abs((try_convert(DECIMAL(9,7),try_CONVERT(BIGINT,IC.last_value)*100/DTM.MaxValue)))
-- obsluga ujemnych wartosci IDENT_CURRENT
update r
SET
ReachMaxValuePercent=1*try_convert(DECIMAL(9,7),ABS(try_convert(BIGINT,-r.MaxDataTypeValue)-try_convert(BIGINT,r.last_value))*100/r.MaxDataTypeValue)
from #report r
where ReachMaxValuePercent>0
and LAST_VALUE<0
UPDATE r
SET DisasterDay=dateadd(DAY,NumberOfDaysFromCreation*100/ReachMaxValuePercent,CReateDate)
FROM #report r
WHERE ReachMaxValuePercent>0
and LAST_VALUE<>0
select DisasterDay,tablename, r.* from #report r
WHERE ReachMaxValuePercent>0
order by r.DisasterDay,r.tablename
DROP TABLE #report
DROP TABLE #DataTypeMaxValue
Dla tych, których takie rozważania nudzą, ciekawe może się wydać, to że przekroczenie zakresu zmiennej całkowitej spowodowało wiele katastrof, które wynikały ze złego oszacowania czasu pracy oprogramowania i zastosowanego typu zmiennej. Warto o tym pomyśleć podczas projektowania rozwiązania bazodanowego na długie lata.
https://bulldogjob.pl/news/114-upiorne-bugi-male-bledy-wielkie-katastrofy
Dodaj komentarz