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

עדי צדק!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  6. כדי לוודא כמה שורות נכנסו לטבלת היעד – בוצע (*)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. לאחר כל פעולה יהיה טוב להדפיס את הזמנים ואת מספר השורות שבוצעו עד כה. – יפתור את הבעיה השלישית.

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

התהליך עצמו ייקח יותר זמן