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.
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******* -SPAR1DB1-61 -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.)
La liste ci-dessous donne quelques exemples d'impuretés de données possibles :
| Type de données | Conditions de pureté |
|---|---|
| Unicode | La longueur des données doit être un multiple de 2. |
| DateTime | La date doit être comprise entre le 1er janvier 1753 et le 31
décembre 9999. Pour le type time, il doit être inférieur à '11:59:59:999PM'. |
| Real / Float | Il ne doit pas exister de valeurs invalides comme SNAN, QNAN, NINF, ND, PD et INF. |
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.
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
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.
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.
| Type de données | Requête de détection |
|---|---|
| Unicode | SELECT col1 ,DATALENGTH(col2) as Length FROM table WHERE DATALENGTH(col2) % 2 != 0 |
| Real / Float | 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) |
| Decimal / Numeric | SELECT col1 FROM table WHERE col2 > 9999999999.99999 OR col2 < -9999999999.99999 Les valeurs dans les clauses WHERE doivent être ajustées en fonction de la précision et de l'échelle de la colonne numeric ou decimal. Dans l'exemple ci-dessus, la colonne col2 est définie en decimal(15,5). |
| Date | SELECT col1 FROM table WHERE col2 < '1/1/1753 12:00:00 AM' OR col2 > '12/31/9999 11:59:59 PM' |
| Time | 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 |
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
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 :
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=PAR1DB4-02;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
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).
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="PAR1DB4-25" State=1 StackTrace:
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(); }
Attention : le test if (var == float.NaN) retourne toujours FALSE même si var est un NaN.
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...
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.
| Version | Date | Commentaires |
|---|---|---|
| 1.0 | 02/2010 | Version initiale |
Troubleshooting DBCC error
2570 in SQL Server 2005
MS SQL Server BOL : DBCC
CHECKDB
MS SQL Server BOL : DBCC
CHECKTABLE