Arithmetic overflow error converting IDENTITY to data type int

January 4, 2017

 

 

לפני זמן מה, קיבלנו פניה מאחד הלקוחות על תקלה במערכות הייצור –

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, לכן, המנוע הוסיף את העמודה בסוף העמודות.

 

עדי צדק!

 

הפתרון החדש שעלה:

  1. ליצור טבלאות חדשות – NewM, NewD עם טיפוסים תואמים לBIGINT

  2.  להעתיק אל  הטבלאות החדשות את הנתונים.

  3. לעצור את הרפליקציה.

  4. ליצור אינדקסים על הטבלאות החדשות.

  5. להפעיל את הרפליקציה.

 

התהליך שבוצע בפועל-  בINSERT יחיד כפעולה אטומית, ל2 הטבלאות M + D, לפסימיים שבינכם(בעלי הניסיון), הפעולה על  טבלה M עבדה בהצלחה (והגדילה את הלוג פי כמה וכמה) אך, הפעולה על טבלת D כמובן, קיבלה Rollback לאחר 14 מיליארד שורות.

לאוהבי הסיפורים, הRollback עדיין ממשיך בימים אילו J

 

מה היו הטעויות בדרך?

  1. העברת נתונים בצורת Fully Logged,

    1. RECOVERY MODEL – נשאר על FULL.

    2. לא יושמו תנאי מצב לטובת Minimally Logged

  2. העתקה בפעולה אטומית אחת לטבלה – מה שעבד בטבלה הקטנה(יחסית) M לא עבד לטבלה הענקית D.

    1. הטעות גרמה לכך שהלוג יגדל משמעותית עד ל0 מקום בכל אחד מהכוננים החדשים שנפתחו בדרך, בניסיון למזער את הטעות.

  3. כדי לוודא כמה שורות נכנסו לטבלת היעד – בוצע (*)SELECT COUNT על טבלת היעד.

בואו ונעשה סדר בכל הבלגן וניראה, איזה אפשרויות עומדות בפינינו -

  1. הדרך המוכרת – כל ההליך שבוצע בתיאור מעלה.

יתרונות – מוכר וידוע לכולם

חסרונות – המון – מפורט מעלה.

  1. הדרך הבטוחה

    א. הקמה של טבלה זהה(אך, בשם שונה NewM), מלבד העמודה המיועדת כ-BIGINT *2 (אחת לכל טבלה). – תחילת המספור של הטבלה יתחיל בדיוק היכן שמסתיים INT מהטבלה המקורית.

    ב. נשנה את השם של טבלת המקור מM לOldM.

    ג. נשנה את הRecovery Model – לSimple/Bulk Logged.

    ד. העתקה של הנתונים מהטבלה המקורית בבאצים.

    1. גודל הבאטצ יקבע לאחר מספר בדיקות שלא מגדיל את הלוג – יישום כ

    2. כל פעולת העתקה תתבצע תחת BEGIN TRAN + COMMIT

    3. נשתמש בset identity_insert

    4. ניתן להשתמש בWhile Or Cursor

SELECT TOP (@BatchSize)

        5. כאשר ההכנסה תתבצע כאשר מקודמים המספרים –

WHERE ID BETWEEN @StartID AND @StartID + @BatchSize

        6. לאחר כל פעולה יהיה טוב להדפיס את הזמנים ואת מספר השורות שבוצעו עד כה. – יפתור את הבעיה השלישית.

חסרונות – יש לכתוב את כל התהליך.

התהליך עצמו ייקח יותר זמן ויגדל ככל שנכניס עוד ועוד מידע לטבלת היעד.

יתרונות

  1. אם והתהליך ייפול באמצע מכל סיבה שהיא, תמיד אפשר להמשיך מנקודת הנפילה.

  2. התהליך לא אמור לנפח את הלוג.    

  3. תוספת של עדי -לא צריך לסיים את כל ההעתקה כדי שהמערכת תעלה.  ניתן להעתיק את הנתונים לתקופה הרלוונטית לפי הגדרת הלקוח, להעלות את המערכת, ולהמשיך ולהכניס את הנתונים היותר ישנים.

  1. הדרך המהירה

  1. הקמה של טבלה זהה(אך, בשם שונה NewM), מלבד העמודה המיועדת כ-BIGINT *2 (אחת לכל טבלה). – תחילת המספור של הטבלה יתחיל בדיוק היכן שמסתיים INT מהטבלה המקורית.

  2. נשנה את השם של טבלת המקור מM לOldM

  3. על כל סט טבלאות, לדוגמא לOldM + NewM להקים View – (AKA Partition View), ובין הטבלאות UNION ALL.

  4. שם הView יקרא בשם הטבלה המקורית M.

  5. כך השליפות יישארו באובייקטיי הקוד בצורה הזהה.

  6. נוסיף טריגר לView כך שפעולות DML יעבדו על האובייקטים הנכונים.  תוספת של עדי – לא מחויב שיהיה צורך בטריגר.  מאחר שצריך לשנות את קוד, שיתמוך בערך של bigint, אפשר את משפט הinsert לכתוב לטבלה החדשה.

יתרונות – הקמה וביצוע תחת זמן קצר.

חסרונות – פגיעה בביצועים, מכיוון שכל פעולת DML מתבצעת תחת טריגר.  תוספת של עדי – צריך לזכור שמדובר בinstead of trigger, כך שלדעתי יש פגיעה זניחה בביצועים (במידה ואכן הולכים על instead of trigger ולא על שינוי בקוד כמו שכתבתי בסעיף הקודם)

  1. הדרך המשולבת

טיפול וביצוע ע"פ סעיף 3 – זמנית.

לקבוע זמן השבתה שמוסכם עם כולם – ולבצע את סעיף 2.

 

  1. דרך נוספת תוספת של עדי – (מאד דומה לדרכים שפורטו) – הקמת טבלה חדשה כpartition table.  מילוי הpartition הראשון, העלאת המערכת ולאחר מכן מילוי של שאר הpartitions.  היתרון הגדול ביותר – טבלה של 20 מיליארד שורות לא אמורה להיות בטבלה אחת רגילה.

  1. דרך נוספת תוספת של עדי – לבצע RESEED לטבלה ע"מ שתתחיל מ-2147483647-, כך יהיה ניתן להמשיך לבצע INSERT על הטבלה.

יתרונות – מאפשר חזרה לשירות בצורה מאוד מהירה.

חסרונות – חייבים לוודא שהאפליקציה תומכת, לדוגמא כאשר אין מקרים שמבצעים INSERT

ע" MAX ID.

דרכים לפתור את מציאת כמות השורות מבלי מבלי לבצע שאילתא ישירות על טבלת המקור

  1. שאילתא על 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);

 

  1. sp_spaceused

  2. שאילתות מפורטות על 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;

 

 

המלצות לשיפור

  1. ספציפית למקרה הזה, מומלץ להוסיף לתהליכי הניטור בדיקה שמזה כמה מתוך העמודות הנ"ל מלאים. ניתן לקרוא בהרחבה מתוך מאמר של גרי רשף (MVP) (B|T) – כאן

 

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);

 

האם עולה לכם רעיון מומלץ שלא יושם בכתוב?

האם קרה לך מקרה דומה? - איך אתה התמודדת עם התקלה?

 

Please reload

Featured Posts

I'm busy working on my blog posts. Watch this space!

Please reload

Recent Posts

October 31, 2017

October 29, 2017

Please reload

Archive
Please reload

Search By Tags