Arithmetic overflow error converting IDENTITY to data type int
לפני זמן מה, קיבלנו פניה מאחד הלקוחות על תקלה במערכות הייצור –
Msg 8115, Level 16, State 1, Line 12
Arithmetic overflow error converting IDENTITY to data type int.
* המאמר מכיל מספר תוספות והערות של עדי כהן, שעבר על המסמך לפני הפרסום.
תחילה כדאי שנצייר מספר קווים כללים כדי להכניס לעלילה.
שרת – SQL Server 2008R2 Ent
גודל מסד הנתונים – (9TB. (AKA VLDB
על השרת הראשי קיימת רפליקציה לשרת משני.
את שגיאה מעלה, הלקוח קיבל כאשר ניסה לבצע פעולת INSERT על אחת הטבלאות במערכת, לצורכנו נקרא לה טבלת (Master(M
הטבלה בעלת עמודת PK – מסוג INT ובעלת (IDENTITY(1,1.
אך, הבעיה היא לא רק בטבלת M. אלא, גם מהטבלה המשנית שקיים לה מפתח זר , לצורך הדוגמא נקרא לה טבלת D)Detail).
הקשר בין הטבלאות הוא יחיד לרבים, כך שטבלת M מונה כ 2 מיליארד שורות(2147483647), גודל כ 140GB (מקווץ)
וטבלת D מונה כ 21 מיליארד שורות – גודל כ- 2TB.
סקריפט לשחזור הבעיה –
דרך א – יצירת הבעיה בקטן ע"י שימוש ב- TINYINT
USE tempdb
GO
IF OBJECT_ID('dbo.XXX','U') IS NOT NULL
DROP TABLE dbo.XXX;
GO
CREATE TABLE XXX([ID] TINYINT NOT NULL IDENTITY(1,1) CONSTRAINT PK_XXX PRIMARY KEY, NOTE VARCHAR(3))
GO
INSERT dbo.XXX ( NOTE )
SELECT name
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 1 AND 255;
GO
INSERT dbo.XXX( NOTE )VALUES ( 'wtf');
דרך ב – יצירת טיפוסים נכונים, הקפצת המספור האוטומטי למספר האחרון.
USE tempdb;
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
DROP TABLE dbo.test;
CREATE TABLE test
(
id INT IDENTITY NOT NULL ,
dummy INT NOT NULL
);
GO
SET IDENTITY_INSERT dbo.test ON;
INSERT INTO test
( id, dummy )
VALUES ( 2147483647, 1 );
SET IDENTITY_INSERT dbo.test OFF;
INSERT INTO test
( dummy )
VALUES ( 1 );
ההצעה לפתרון הבעיה שעלתה יחד עם הלקוח הייתה, לשנות את העמודה בטבלאות לטיפוס BIGINT, ולמצוא את כל האזכורים בקוד ולוודא תקינות לטיפוס החדש.
תחילה חשבתי, שאם הלקוח היה בשרת מסוג SQL Server 2012 ומעלה המנוע מתנהג אחרת והיה ניתן לשנות מבלי לחשוש(נחזור לנקודה זו בהמשך).
אבל, כפי שראינו סוג השרת הינו SQL Server 2008R2, בגרסת המוצר הזאת כאשר מבצעים שינוי
Meta Data מהסוג הזה, הטבלה ננעלת ומתבצעת פעולה 1 טרנזקטיבית עד לסיום. כך אנו עלולים
לקבל נעילה ארוכה ותקווה מאוד גדולה שהפעולה לא תכשל במהלך הזמן.
לפחות זאת המחשבה שהתנגנה לי בראש, אך כאשר עדי כהן (יועץ בסיסי נתונים בכיר), עבר על המסמך, דעתו הייתה שונה – לדעתו של עדי, גם בגרסאות מאוחרות יותר הייתה פעולה של שינוי כל שדה בדפים ולא רק פעולת metadata. פעולות metadata מתבצעות בהוספה של עמודה, בהורדה של עמודה ובשינוי גודל של עמודות בגודל משתנה לגודל גבוה מהגודל נוכחי.
החלטתי לבדוק זאת,
ניקח טבלה וניצור אותה בtempdb, נבדוק כמה ואיפה היא תופסת מקום, לאחר מכן נשמנה את השדה הראשון מסוג INT לסוג BIGINT, וניראה איפה בדפים יתרחש השינוי (את הסקריפט הרצתי על מכונה מסוג 2014, כך שאם אני צודק, לא תהיה נגיעה בדפים) –
USE tempdb;
GO
IF OBJECT_ID('dbo.AlterDemo','U') IS NOT NULL
DROP TABLE dbo.AlterDemo;
GO
CREATE TABLE AlterDemo (IntColumn INT NOT NULL,Int2ndColumn INT NOT NULL,VarCharColumn VARCHAR(6) NOT NULL);
GO
SELECT C.column_id,C.name [ColumnName],t.name [Type],SIPC.max_inrow_length,SIPC.leaf_offset
FROM sys.system_internals_partition_columns SIPC
INNER JOIN sys.partitions P ON P.partition_id = SIPC.partition_id
INNER JOIN sys.columns C ON C.object_id = P.object_id
AND C.column_id = SIPC.partition_column_id
INNER JOIN sys.types T ON T.system_type_id = SIPC.system_type_id
WHERE C.object_id = OBJECT_ID('dbo.AlterDemo','U');
GO
ALTER TABLE dbo.AlterDemo ALTER COLUMN IntColumn BIGINT NOT NULL
GO
SELECT C.column_id,C.name [ColumnName],t.name [Type],SIPC.max_inrow_length,SIPC.leaf_offset
FROM sys.system_internals_partition_columns SIPC
INNER JOIN sys.partitions P ON P.partition_id = SIPC.partition_id
INNER JOIN sys.columns C ON C.object_id = P.object_id
AND C.column_id = SIPC.partition_column_id
INNER JOIN sys.types T ON T.system_type_id = SIPC.system_type_id
WHERE C.object_id = OBJECT_ID('dbo.AlterDemo','U');
GO
DROP TABLE dbo.AlterDemo;
כפי שניתן לראות בפלט, העמודה התווספה בסוף האופסט, מכוון שלא נשאר מקום לעמודה לגדול.
גודל עמודת INT היא מסוג 4Byte ועמודה מסוג BIGINT הינה 8Byte, לכן, המנוע הוסיף את העמודה בסוף העמודות.
עדי צדק!
הפתרון החדש שעלה:
ליצור טבלאות חדשות – NewM, NewD עם טיפוסים תואמים לBIGINT
להעתיק אל הטבלאות החדשות את הנתונים.
לעצור את הרפליקציה.
ליצור אינדקסים על הטבלאות החדשות.
להפעיל את הרפליקציה.
התהליך שבוצע בפועל- בINSERT יחיד כפעולה אטומית, ל2 הטבלאות M + D, לפסימיים שבינכם(בעלי הניסיון), הפעולה על טבלה M עבדה בהצלחה (והגדילה את הלוג פי כמה וכמה) אך, הפעולה על טבלת D כמובן, קיבלה Rollback לאחר 14 מיליארד שורות.
לאוהבי הסיפורים, הRollback עדיין ממשיך בימים אילו J
מה היו הטעויות בדרך?
העברת נתונים בצורת Fully Logged,
RECOVERY MODEL – נשאר על FULL.
לא יושמו תנאי מצב לטובת Minimally Logged
העתקה בפעולה אטומית אחת לטבלה – מה שעבד בטבלה הקטנה(יחסית) M לא עבד לטבלה הענקית D.
הטעות גרמה לכך שהלוג יגדל משמעותית עד ל0 מקום בכל אחד מהכוננים החדשים שנפתחו בדרך, בניסיון למזער את הטעות.
כדי לוודא כמה שורות נכנסו לטבלת היעד – בוצע (*)SELECT COUNT על טבלת היעד.
בואו ונעשה סדר בכל הבלגן וניראה, איזה אפשרויות עומדות בפינינו -
הדרך המוכרת – כל ההליך שבוצע בתיאור מעלה.
יתרונות – מוכר וידוע לכולם
חסרונות – המון – מפורט מעלה.
הדרך הבטוחה –
א. הקמה של טבלה זהה(אך, בשם שונה NewM), מלבד העמודה המיועדת כ-BIGINT *2 (אחת לכל טבלה). – תחילת המספור של הטבלה יתחיל בדיוק היכן שמסתיים INT מהטבלה המקורית.
ב. נשנה את השם של טבלת המקור מM לOldM.
ג. נשנה את הRecovery Model – לSimple/Bulk Logged.
ד. העתקה של הנתונים מהטבלה המקורית בבאצים.
גודל הבאטצ יקבע לאחר מספר בדיקות שלא מגדיל את הלוג – יישום כ
כל פעולת העתקה תתבצע תחת BEGIN TRAN + COMMIT
נשתמש בset identity_insert
ניתן להשתמש בWhile Or Cursor
SELECT TOP (@BatchSize)
5. כאשר ההכנסה תתבצע כאשר מקודמים המספרים –
WHERE ID BETWEEN @StartID AND @StartID + @BatchSize
6. לאחר כל פעולה יהיה טוב להדפיס את הזמנים ואת מספר השורות שבוצעו עד כה. – יפתור את הבעיה השלישית.
חסרונות – יש לכתוב את כל התהליך.
התהליך עצמו ייקח יותר זמן ויגדל ככל שנכניס עוד ועוד מידע לטבלת היעד.
יתרונות –
אם והתהליך ייפול באמצע מכל סיבה שהיא, תמיד אפשר להמשיך מנקודת הנפילה.
התהליך לא אמור לנפח את הלוג.
תוספת של עדי -לא צריך לסיים את כל ההעתקה כדי שהמערכת תעלה. ניתן להעתיק את הנתונים לתקופה הרלוונטית לפי הגדרת הלקוח, להעלות את המערכת, ולהמשיך ולהכניס את הנתונים היותר ישנים.
הדרך המהירה
הקמה של טבלה זהה(אך, בשם שונה NewM), מלבד העמודה המיועדת כ-BIGINT *2 (אחת לכל טבלה). – תחילת המספור של הטבלה יתחיל בדיוק היכן שמסתיים INT מהטבלה המקורית.
נשנה את השם של טבלת המקור מM לOldM
על כל סט טבלאות, לדוגמא לOldM + NewM להקים View – (AKA Partition View), ובין הטבלאות UNION ALL.
שם הView יקרא בשם הטבלה המקורית M.
כך השליפות יישארו באובייקטיי הקוד בצורה הזהה.
נוסיף טריגר לView כך שפעולות DML יעבדו על האובייקטים הנכונים. תוספת של עדי – לא מחויב שיהיה צורך בטריגר. מאחר שצריך לשנות את קוד, שיתמוך בערך של bigint, אפשר את משפט הinsert לכתוב לטבלה החדשה.
יתרונות – הקמה וביצוע תחת זמן קצר.
חסרונות – פגיעה בביצועים, מכיוון שכל פעולת DML מתבצעת תחת טריגר. תוספת של עדי – צריך לזכור שמדובר בinstead of trigger, כך שלדעתי יש פגיעה זניחה בביצועים (במידה ואכן הולכים על instead of trigger ולא על שינוי בקוד כמו שכתבתי בסעיף הקודם)
הדרך המשולבת –
טיפול וביצוע ע"פ סעיף 3 – זמנית.
לקבוע זמן השבתה שמוסכם עם כולם – ולבצע את סעיף 2.
דרך נוספת תוספת של עדי – (מאד דומה לדרכים שפורטו) – הקמת טבלה חדשה כpartition table. מילוי הpartition הראשון, העלאת המערכת ולאחר מכן מילוי של שאר הpartitions. היתרון הגדול ביותר – טבלה של 20 מיליארד שורות לא אמורה להיות בטבלה אחת רגילה.
דרך נוספת תוספת של עדי – לבצע RESEED לטבלה ע"מ שתתחיל מ-2147483647-, כך יהיה ניתן להמשיך לבצע INSERT על הטבלה.
יתרונות – מאפשר חזרה לשירות בצורה מאוד מהירה.
חסרונות – חייבים לוודא שהאפליקציה תומכת, לדוגמא כאשר אין מקרים שמבצעים INSERT
ע" MAX ID.
דרכים לפתור את מציאת כמות השורות מבלי מבלי לבצע שאילתא ישירות על טבלת המקור
שאילתא על sys.partition – ניתן לראות השוואות כאן
DECLARE @TableName sysname
SET @TableName = 'M'
SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(@TableName)
AND index_id < 2
GROUP BY OBJECT_NAME(object_id);
שאילתות מפורטות על DMV
SELECT t.name AS TableName ,
s.name AS SchemaName ,
p.rows AS RowCounts ,
SUM(a.total_pages) * 8 AS TotalSpaceKB ,
SUM(a.used_pages) * 8 AS UsedSpaceKB ,
( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.object_id > 255
GROUP BY t.name ,s.name , p.rows
ORDER BY t.name;
המלצות לשיפור
Declare @SQL Varchar(Max);
Select @SQL=IsNull(@SQL+' Union All'+Char(13),'')+Concat('Select ''',Object_Schema_Name(Cl.object_id),'.',T.name,''' Object, ''Column ',Cl.name,''' ObjectType, Ident_Current(''[',Object_Schema_Name(Cl.object_id),'].[',Object_Name(Cl.object_id),']'') MaxIdentity, Case ''',Tp.name,''' When ''int'' Then 2147483647 When ''smallint'' then 32767 When ''tinyint'' Then 255 When ''bigint'' Then 9223372036854775807 Else Null End UpperLimit')
From sys.columns Cl
Inner Join sys.types Tp On Cl.system_type_id=Tp.system_type_id
And Cl.user_type_id=Tp.user_type_id
Inner Join sys.tables T On Cl.object_id=T.object_id
Where Tp.name In ('int','smallint','bigint','tinyint')
And Cl.is_identity=1;
Set @SQL=Concat(@SQL,' Union All',Char(13),'Select Concat('''',Schema_Name(S.schema_id),''.'',S.name,'''') Object, ''Sequence'' ObjectType, S.current_value MaxIdentity, S.maximum_value UpperLimit From sys.sequences S Inner Join sys.types Tp On S.system_type_id=Tp.system_type_id And S.user_type_id=Tp.user_type_id Where Tp.name=''int''');
Set @SQL=Concat(@SQL,Char(13),'Order By MaxIdentity Desc;');
Exec(@SQL);
האם עולה לכם רעיון מומלץ שלא יושם בכתוב?
האם קרה לך מקרה דומה? - איך אתה התמודדת עם התקלה?
