MS SQL Server 2005 et l’option WITH DATA_PURITY des commandes DBCC CHECKDB et DBCC CHECKTABLE.

Introduction

SQL Server 2005 introduit la nouvelle option WITH DATA_PURITY dans les commandes de vérification d’intégrité DBCC CHECKDB et DBCC CHECKTABLE. L’option WITH DATA_PURITY vérifie la pureté des données et s’avère particulièrement importante pour les bases de données créées avec les versions antérieures à SQL Server 2005 (SQL Server 2000 et SQL Server 7.0).

Dans cet article, les symptômes rencontrés lorsque des impuretés de données sont détectées par SQL Server 2005 sont décrits puis les méthodes de détection et de correction sont proposées. Les symptômes sont rencontrés dans un contexte de migration d’une base de données SQL Server 2000 vers SQL Server 2005 via la méthode BACKUP/RESTORE.

Un exemple pratique d’injection de données invalides dans une base SQL Server 2000 avec une application .NET est proposé. Des valeurs NaN (Not A Number) et INF (Infinity) sont injectées dans une colonne de type float. SQL Server 2005 détecte ces impuretés lors de la migration de la base de données SQL Server 2000 par BACKUP/RESTORE.

L’article montre également avec un exemple en langage C# le caractère nettement moins permissif de SQL Server 2005 sur l’injection de données NaN, Infinity ou en dehors des intervalles.

Symptômes rencontrés avec les impuretés de données (Msg 9100, possible index corruption)

Lorsque des impuretés de données sont détectées par SQL Server 2005, les symptômes peuvent être très variés mais généralement le message 9100 évoquant une corruption potentielle d’un index apparaît dans le fichier de log du serveur SQL Server 2005 lorsque ces données invalides sont accédées.

Msg 9100, Level 23, State 2, Line 1 Possible index
corruption detected. Run DBCC CHECKDB.

L’utilitaire bcp d’export/import des données retourne une erreur (SQLState 22003 pour des impuretés dans des types numériques par exemple) et s’arrête lorsque le nombre maximum d’erreurs est atteint (option -m du binaire bcp, par défaut 10).

bcp thinkfolio..TradeMarketSide out TradeMarketSide.bcpc -Usa -P******* -S SRVWINFR1 -c
...
1000 rows successfully bulk-copied to host-file. Total received: 202000
 ...
SQLState = 22003, NativeError = 0
Error = [Microsoft][SQL Native Client]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][SQL Native Client]Numeric value out of range

202708 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 40953  Average : (4949.77 rows per sec.)

Description des impuretés de données

La liste ci-dessous donne quelques exemples d’impuretés de données possibles :

Il est impossible d’avoir des impuretés de données pour certains typages de données. Par exemple le type tinyint (0 à 255) stocké dans un byte ne peut jamais présenter de valeurs hors intervalle.

La nouvelle option SQL Server 2005 WITH DATA_PURITY des commandes DBCC CHECKDB et DBCC CHECKTABLE (Msg 2570)

SQL Server 2005 introduit la nouvelle option WITH DATA_PURITY dans les commandes DBCC CHECKDB et DBCC CHECKTABLE pour détecter les objets qui comportent des impuretés de données.

DBCC CHECKDB (database_name | database_id | 0) WITH DATA_PURITY

DBCC CHECKTABLE (table_name | view_name ) WITH DATA_PURITY

Dans la commande DBCC CHECKDB, lorsque la valeur 0 est donnée en paramètre, la base courante est analysée.

L’option WITH DATA_PURITY effectue la validation des données pour toutes les lignes et colonnes des tables. Seuls les types de données pouvant présenter des valeurs hors intervalle sont vérifiés (unicode, datetime, real/float, numeric/decimal).

Á l’issue d’une migration d’une base de données SQL Server 2000 ou SQL Server 7.0 vers SQL Server 2005 par la méthode BACKUP/RESTORE, il est recommandé de lancer la commande DBCC CHECKDB avec l’option WITH DATA_PURITY pour détecter les impuretés de données. Le message 2570 est levé lorsqu’une valeur incorrecte est trouvée.

DBCC CHECKDB(0) WITH DATA_PURITY
...
DBCC results for 'TradeMarketSide'.
Msg 2570, Level 16, State 3, Line 3
Page (1:411415), slot 6 in object ID 405576483, index ID 1, partition ID 72057594078691328, 
alloc unit ID 72057594081902592 (type "In-row data"). 
Column "dfRateForward" value is out of range for data type "float".  Update column to a legal value.
 ...
Msg 2570, Level 16, State 3, Line 3
Page (1:411463), slot 3 in object ID 405576483, index ID 1, partition ID 72057594078691328, 
alloc unit ID 72057594081902592 (type "In-row data"). 
Column "dfBuyAmountForwardMarket" value is out of range for data type "float".  Update column to a legal value.
 ...
There are 972543 rows in 79793 pages for object "TradeMarketSide".
CHECKDB found 0 allocation errors and 4080 consistency errors in table 'TradeMarketSide' (object ID 405576483).

Le message 8986 (Too many errors) apparaît lorsque trop d’erreurs 2570 sont détectées dans un objet :

Msg 8986, Level 16, State 1, Line 3
Too many errors found (201) for object ID 405576483. 
To see all error messages rerun the statement using "WITH ALL_ERRORMSGS".

L’option WITH ALL_ERRORMSGS doit être ajoutée dans la commande DBCC CHECKTABLE ou DBCC CHECKDB pour obtenir le rapport complet des valeurs invalides.

Exemple :

DBCC CHECKTABLE ('TradeMarketSide') WITH DATA_PURITY, ALL_ERRORMSGS

Conditions de vérifications des impuretés de données

Les vérifications de la pureté des données ne sont pas activées automatiquement pour toutes les bases de données. Les vérifications sont réalisées selon plusieurs facteurs.

• Pour les bases de données créées avec SQL Server 2005, les vérifications de pureté sont activées par défaut et ne peuvent pas être désactivées. L’option WITH DATA_PURITY n’est pas nécessaire dans l’exécution régulière des commandes de vérification d’intégrité DBCC CHECKTABLE et DBCC CHECKDB.
• Pour les bases de données créées avec SQL Server 2000 ou SQL Server 7.0 et migrées vers SQL Server 2005, les détections des impuretés de données ne sont pas activées par défaut. Il faut exécuter la commande DBCC CHECKDB WITH DATA_PURITY dès que la base est migrée vers SQL Server 2005 et corriger les valeurs invalides éventuellement détectées. 2 cas de figure se présentent :
  • La commande DBCC CHECKDB avec l’option WITH DATA_PURITY indique que la base est saine. Cette information est alors enregistrée dans l’entête de la base de données et l’option WITH DATA_PURITY n’est plus nécessaire dans les exécutions ultérieures de la commande DBCC CHECKDB. Les vérifications d’impuretés de données sont alors activées comme s’il s’agissait d’une base de données créées avec SQL Server 2005.
  • La commande DBCC CHECKDB avec l’option WITH DATA_PURITY reporte des données invalides hors intervalle et dans ce cas, celles-ci doivent être corrigées. La commande DBCC CHECKDB avec l’option WITH DATA_PURITY doit être exécutée jusqu’à un état sain de la base de données pour se retrouver dans le cas plus haut.
• Si l’option PHYSICAL_ONLY est spécifiée dans les commandes DBCC CHECKTABLE et DBCC CHECKDB, la validité des données n’est pas vérifiée.

Retrouver des données hors intervalle et des données NaN (Not A Number) avec des requêtes T-SQL

Retrouver les données hors intervalle

Il est possible de retrouver avec des requêtes Transact SQL les lignes contenant des données hors intervalle pour les types de données Unicode, Real, Float, Decimal, Numeric, DateTime.

Dans les exemples ci-dessous, la colonne col2 contient la valeur hors intervalle et la colonne col1 correspond à la clé unique de la table.

SELECT col1 ,DATALENGTH(col2) as Length 
FROM table
WHERE DATALENGTH(col2) % 2 != 0

SELECT col1 FROM table
WHERE col2<>0.0 
AND (col2 < 2.23E-308 OR col2 > 1.79E+308) 
AND (col2 < -1.79E+308 OR col2 > -2.23E-308)

SELECT col1 FROM table
WHERE col2 > 9999999999.99999 
OR col2 < -9999999999.99999

SELECT col1 FROM table
WHERE col2 < '1/1/1753 12:00:00 AM' 
OR col2 > '12/31/9999 11:59:59 PM'

SELECT col1 FROM table 
WHERE((DATEPART(ms,col2)+ 
(1000*DATEPART(s,col2))
 + (1000*60*DATEPART(mi,col2)) 
 + (1000*60*60*DATEPART(hh,col2)))/(1000*0.00333)) > 25919999

Retrouver les valeurs NaN (Not A Number)

Lorsque des valeurs NaN sont écrites dans des colonnes de type numérique, leur interrogation génère l’erreur 3628 :

select id from t_purity where rx_value 0
          Msg 3628, Level 16, State 1, Line 1
A floating point exception occurred in the user process. Current transaction is canceled.

Pour retrouver les valeurs NaN (Not a Number) dans les colonnes de type numérique, il n’y a pas d’autre alternative que de convertir les données de la colonne en varchar et de tester la chaîne varchar obtenue avec la fonction isnumeric :

select case isnumeric(convert(varchar(100), coalesce(colname,0))) when 1 then 0 else 1 end
from tablename

Corriger les impuretés de données

Les erreurs 2570 ne peuvent pas être automatiquement corrigées avec les commandes DBCC car il est impossible pour la commande DBCC de déterminer la bonne valeur fonctionnelle à écrire.

Une mise à jour manuelle de la valeur du champ doit être réalisée pour les lignes présentant les impuretés, mise à jour manuelle avec les commandes UPDATE et/ou DELETE et qui ne peut pas être réalisée sans accompagnement des équipes fonctionnelles de l’application.

Les choix de correction sont multiples :

• Si la valeur fonctionnelle valide est connue, le champ est mis à jour avec cette valeur.
• Application de la valeur NULL au champ.
• Application de la valeur minimum ou maximum du type de données de la colonne.
• Suppression de la ligne si celle-ci n’est pas fonctionnellement nécessaire à l’application.

Simulation d’une injection de données invalides dans SQL Server 2000 avec C# .NET

Injection de données Not A Number et Infinity dans SQL Server 2000

Voici un cas d’école pour simuler l’injection de données invalides dans SQL Server 2000 avec C# .NET.

Les données invalides sont écrites dans une colonne de type float et l’exemple injecte des données NaN (Not a Number) et INF (Infinity).

La structure de la table de démonstration est la suivante :

create table t_purity 
( 
   id int not null, 
   rxvalue float null
)
go

Avec C#, quelques fonctions mathématiques simples permettent de retourner des variables double ayant des valeurs invalides INFINITY et NaN :

1.0/0.0 => double.infinity
Math.Asin(2) => double.NaN

Le "simple" bout de code C# ci-dessous injecte les données invalides INFINITY et NaN dans la colonne rxvalue de la table t_infinity.

static void Main(string[] args)
  {
    using (SqlConnection connection =
            new SqlConnection("Data Source=SRVWINFR1;Integrated Security=true;Initial Catalog=SPA;"))
     {
        connection.Open();

        SqlCommand InsereInvalidValues = new SqlCommand(
                    "insert into t_purity values (@id,@rxvalue)",
                    connection);

        InsereInvalidValues.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4));
        InsereInvalidValues.Parameters.Add(new SqlParameter("@rxvalue", SqlDbType.Float, 8));

        // Insertion de la première ligne avec la valeur INFINITY
        InsereInvalidValues.Parameters["@id"].Value = 1;
        InsereInvalidValues.Parameters["@rxvalue"].Value = 1.0/0.0;
  
        InsereInvalidValues.ExecuteNonQuery();
        
        // Insertion de la deuxième ligne avec la valeur NaN
        InsereInvalidValues.Parameters["@id"].Value = 2;
        InsereInvalidValues.Parameters["@rxvalue"].Value = Math.Asin(2);

        InsereInvalidValues.ExecuteNonQuery();
   }
}

La table t_purity avec SQL Server 2000 accepte bien les données invalides. La colonne float est convertie en type varchar pour la lisibilité de la valeur NaN (-1.#IND) :

select id, convert(varchar(100),rxvalue) as rxvalue,
from t_purity
go

id   rxvalue
---  ----------------------
1    1.#INF
2    -1.#IND

Migration de la base SQL Server 2000 vers SQL Server 2005

La base SQL Server 2000 utilisée pour simuler l’injection des données invalides est ensuite migrée vers SQL Server 2005 par la méthode backup database/restore database. La commande DBCC CHECKDB WITH DATA_PURITY détecte bien les données invalides dans la table t_purity injectées dans la version SQL Server 2000 :

DBCC CHECKDB('SPA') WITH DATA_PURITY
DBCC results for 't_purity'.
Msg 2570, Level 16, State 3, Line 1
Page (1:75), slot 0 in object ID 1977058079, index ID 0, 
partition ID 129568478265344, alloc unit ID 129568478265344 (type "In-row data"). 
Column "rxvalue" value is out of range for data type "float".  
Update column to a legal value.
Msg 2570, Level 16, State 3, Line 1
Page (1:75), slot 1 in object ID 1977058079, index ID 0, 
partition ID 129568478265344, alloc unit ID 129568478265344 (type "In-row data").
Column "rxvalue" value is out of range for data type "float".
Update column to a legal value.
There are 2 rows in 1 pages for object "t_purity".
CHECKDB found 0 allocation errors and 2 consistency errors in table 't_purity' (object ID 1977058079).

Tentative d’injection de données Not A Number et Infinity avec SQL Server 2005

L’option WITH DATA_PURITY de la commande DBCC CHECKDB est bien apparue dans SQL Server 2005 pour détecter des données invalides créées dans des versions antérieures (SQL Server 2000, SQL Server 7.0…) car ces données non conformes sont désormais refusées par la version 2005. Le même code plus haut testé sur une base SQL Server 2005 est refusée avec une exception SqlException :

System.Data.SqlClient.SqlException was unhandled
  Message="The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect.
Parameter 4 (\"@rxvalue\"): The supplied value is not a valid instance of data type float. 
Check the source data for invalid values. An example of an 
invalid value is data of numeric type with scale greater than precision."
  Source=".Net SqlClient Data Provider"
  ErrorCode=-2146232060
  Class=16
  LineNumber=1
  Number=8023
  Procedure=""
  Server="SRVWINFR1"
  State=1
  StackTrace:

Tester la validité des données avec C# pour les versions antérieures à SQL Server 2005

SQL Server 2005 renvoie une exception en cas de valeurs invalides NaN, Infinity, etc., mais pour les versions antérieures SQL Server 2000, SQL Server 7.0…, les tests de validité des données doivent être réalisés en amont dans le code.

Voici un exemple de test pour les valeurs Infinity et NaN pour l’exemple plus haut grâce aux fonctions IsInfinity et IsNaN :

double var1 = 1.0/0.0;
if (double.IsInfinity(var1))
  {
    Console.WriteLine("Infinity");
  }
else
  {
    InsereInvalidValues.Parameters["@id"].Value = 1;
    InsereInvalidValues.Parameters["@rxvalue"].Value = var1;
    InsereInvalidValues.ExecuteNonQuery();
  }


double var2 = Math.Asin(2);
if (double.IsNaN(var2))
  {
    Console.WriteLine("Not a Number");
  }
else
  {
    InsereInvalidValues.Parameters["@id"].Value = 2;
    InsereInvalidValues.Parameters["@rxvalue"].Value = var2;
    InsereInvalidValues.ExecuteNonQuery();
  }

Les types de données avec C# proposent les constantes MinValue et MaxValue pour connaître les bornes inférieure et supérieure afin de tester les valeurs hors intervalle, par exemple double.MinValue, double.MaxValue, etc.

Conclusion

Toutes les bases de données créées avec SQL Server 7.0 ou SQL Server 2000 et migrées vers SQL Server 2005 par les méthodes BACKUP/RESTORE ou sp_attach_db doivent être vérifiées avec l’option WITH DATA_PURITY de la commande DBCC CHECKDB. SQL Server 2005 est beaucoup plus vigilant et moins permissif sur la qualité des données par rapport à SQL Server 2000 ou SQL Server 7.0.

Dès que la base est saine, l’option WITH DATA_PURITY n’est plus nécessaire dans les commandes DBCC CHECKDB et DBCC CHECKTABLE, les vérifications de la validité des données sont alors actives par défaut.

Toutes les applications encore développées avec des versions de SQL Server antérieures à SQL Server 2005 doivent prendre toutes les précautions nécessaires pour éviter des injections de valeurs NaN, Infinity ou hors intervalle. Si ces précautions ne sont pas prises, les migrations vers SQL Server 2005 engendrent des erreurs 2570 tant que les incohérences ne sont pas corrigées.

Les corrections ne peuvent pas être réalisées sans solliciter l’équipe fonctionnelle de l’application.