Ne pas déclencher de warning sur une query Insert

**Delioneras** · 24/09/2018, 18h37

Bonjour à tous et à toutes,

J'ai une question un peu particulière concernant les warnings de MySQL.

Que j'explique un peu le contexte.

Dans ma boite, nous exécutons un certain nombre de traitements automatiques sur nos bases de données, importants dans les cycles de vie des métiers de nos clients, et chacun de ces traitements possèdent un type et génèrent un certain nombre de compte-rendus (erreurs, stats, états, etc ...) associés à ce type, le tout, stocké en DB.

Ces traitements sont instanciés en PHP, suivent une architecture commune à l'ensemble des traitements, et ont pour but d'appliquer au maximum le fait de faire les calculs au maximum dans la DB. C'est au plus proche de la donnée, on fait du traitement ensembliste et non pas séquentiel, et on peut tout annuler avec un seul rollback de transaction.

L'architecture habituelle de ce genre de traitements est la suivante:
1/ Initialisation du référentiel de traitement + initialisation du moteur de compte-rendus.

2/ Initialisation des données préalables en DB => il s'agit là de charger les données dans des tables créées par le traitement et détruites à la fin de ce dernier. On y charge temporairement les données sur lesquelles on doit travailler.

3/ Vérification des conditions pré requises => vérification des différents formats, ce genre de choses. C'est à cet endroit que l'on détermine les éléments bloquants qui empêchent le traitement de continuer.

4/ Exécution du traitement en lui-même si contrôles OK => calcul des données temporaires, ajouts et modifications des tables réelles

5/ fin du traitement et clôture des compte-rendus associés.

Maintenant que le contexte est posé, pour en revenir à mon problème:
J'ai une table avec la structure suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE tmp_maj_dms(  
  dms_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  dms_numero VARCHAR(20) NOT NULL COMMENT 'numero sva format international sans signe',
  dms_date_mes VARCHAR(15) NOT NULL COMMENT 'date mes format AAAAMMJJ (texte)',
  dms_date_mes_formatee DATE COMMENT 'date mes format AAAA-MM-JJ (date)',
  fk_num_id INT(10) UNSIGNED COMMENT 'fk id numero_num',
  dms_user_id int(10) unsigned NOT NULL COMMENT 'id du user ayant lance le traitement',
  PRIMARY KEY (dms_id),
  CONSTRAINT fk_dms_num FOREIGN KEY (fk_num_id) REFERENCES numero_num(num_id)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci";

Dans laquelle je charge les données issues d'un fichier csv (un numéro de tel sur 10 caractères, et une date au format AAAAMMJJ).
Ces informations vont respectivement dans les champs "dms_numero" et "dms_date_mes".

Lors d'un de mes contrôles prérequis, je veux valider que la date correspond bien à une date valide.
Pour se faire, j'utilise la requête suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT dms.dms_numero AS param1, dms.dms_date_mes AS param2
FROM tmp_maj_dms AS dms
WHERE DATE(dms.dms_date_mes) IS NULL
OR dms.dms_date_mes NOT RLIKE '[0-9]{8}'

L'idée est de lister les numéros pour lesquelles les dates associées ne sont pas au bon format OU ne sont pas une date valide.
Pour cela, le select est associé à un insert into dans notre table des compte-rendus qui prend des valeurs textuelles.

La requête s'exécute à merveille et fait parfaitement son boulot (les numéros sont bien listés dans les compte-rendus).
En revanche, elle lève un warning (invalid date format) qui est interprété par PHP comme une Exception, qui me bloque l'ensemble du traitement par la suite (alors que c'est pour moi le comportement attendu).

J'avoue ma détresse quant à la création d'une expression régulière qui me valide une date réelle (valider un format de date est facile, mais valider la réalité d'une date est ... bref).

Je voudrais juste faire taire ce warning à ce moment précis pour que PHP ne lève pas d'exception.

Vous auriez une idée d'une requête que je pourrais passer au SGBD pour mettre en silence les warnings ?

Configuration:
MySQL 5.5.9
PHP: 5.4.23

En vous remerciant par avance,
Cordialement,
Delio

**Artemus24** · 25/09/2018, 11h28

Salut Delioneras.

Comme il arrive parfois que la date ne soit pas correcte, votre principal problème est de la convertir sans produire une erreur.
En fait, ce ne sont pas des "warnings" mais des "errors" qui sont produits, comme celui-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ERROR 1411 (HY000) at line 48: Incorrect datetime value: '54/09/2018' for function str_to_date

Et on ne peut pas les désactiver. Si ce sont des "notes", oui, on peut le faire.

Dans cet exemple, j'utilise une fonction pour convertir une date du format "/DD/MM/YYYY" en "YYYY-MM-DD".
Et je teste la validité de la date afin de ne pas produire une erreur.
Si la date n'est pas valide alors je retourne "NULL".

Voici mon jeu d'essai :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
1;\N
2;12/07/2018
3;54/09/2018
4;04/19/2018
5;04/11/9999
6;31/04/2017
7;27/10/2018
8;29/02/2013

Et voici le résultat que cela produit au chargement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `trav`
--------------
 
--------------
CREATE TABLE `trav`
( `id`    integer unsigned   not null auto_increment primary key,
  `date`  date                   null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
CREATE FUNCTION chgdate(in_date text) RETURNS datetime
  DETERMINISTIC
  LANGUAGE SQL
BEGIN
  DECLARE _jour  CHAR(02) DEFAULT NULL;
  DECLARE _mois  CHAR(02) DEFAULT NULL;
  DECLARE _annee CHAR(04) DEFAULT NULL;
  DECLARE _test  SMALLINT DEFAULT NULL;
 
  SET _jour  =                 SUBSTRING_INDEX(in_date,'/', 1);
  SET _annee =                 SUBSTRING_INDEX(in_date,'/',-1);
  SET _mois  = SUBSTRING_INDEX(SUBSTRING_INDEX(in_date,'/', 2),'/',-1);
 
  IF (_jour  <   '01') || (_jour       >   '31') THEN return NULL; END IF;
  IF (_mois  <   '01') || (_mois       >   '12') THEN return NULL; END IF;
  IF (_annee < '1971') || (_annee      > '2023') THEN return NULL; END IF;
 
  IF (_mois = '04') || (_mois = '06') || (_mois = '09') || (_mois = '11') THEN
        SET _test = 1;
  ELSE  SET _test = 0;
  END IF;
 
  IF (_jour  =   '31') && (_test       = 1) THEN return NULL; END IF;
  IF (_mois  =   '02') && (_annee % 4 <> 0) THEN return NULL; END IF;
 
  return str_to_date(in_date, '%d/%m/%Y');
END
--------------
 
--------------
SET sql_notes = 0
--------------
 
--------------
LOAD DATA INFILE 'E:/30.MySql/40.Load/Ex_07/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1,@F2)
    SET `date`= chgdate(@F2)
--------------
 
--------------
select * from trav
--------------
 
+----+------------+
| id | date       |
+----+------------+
|  1 | NULL       |
|  2 | 2018-07-12 |
|  3 | NULL       |
|  4 | NULL       |
|  5 | NULL       |
|  6 | NULL       |
|  7 | 2018-10-27 |
|  8 | NULL       |
+----+------------+
 
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 0
--------------
 
Appuyez sur une touche pour continuer...

@+

**SQLpro** · 25/09/2018, 11h41

Si vous utilisiez SQL Server de Microsoft vous pourriez utiliser la fonction TRY_CAST(...) qui permet de tenter le transtypage et renvoi NULL en cas d'erreur de type au lieu de planter....

Mais rien ne vous empêche de créer vos propres fonctions de la sorte.

A +

**Delioneras** · 25/09/2018, 12h04

Bonjour et merci à vous deux pour vos réponses,

J'ai oublié de préciser que, prérogatives de l'entreprise cliente, tout ce qui est function, trigger, stored procedure, et view sont interdits sur les serveurs.

J'ai déprimé sévère quand j'ai découvert cela, et oui, SQL Server me manque beaucoup (les ... errements de MySQL me font froid dans le dos).

Bon ... si c'est vraiment une exception levée et que je ne peux pas l'empêcher, je vais essayer de la catcher à un plus haut niveau.

Encore merci à vous deux.

**SQLpro** · 26/09/2018, 11h12

Envoyé par Delioneras

Bonjour et merci à vous deux pour vos réponses,

J'ai oublié de préciser que, prérogatives de l'entreprise cliente, tout ce qui est function, trigger, stored procedure, et view sont interdits sur les serveurs.

J'ai déprimé sévère quand j'ai découvert cela, et oui, SQL Server me manque beaucoup (les ... errements de MySQL me font froid dans le dos).

Bon ... si c'est vraiment une exception levée et que je ne peux pas l'empêcher, je vais essayer de la catcher à un plus haut niveau.

Encore merci à vous deux.

Quand on a des clients aussi con que ceux là, on en change ! Ils se tirent une balle dans le pied....

A +

**Artemus24** · 25/09/2018, 12h14

Salut SQLPRO.

Quelque chose dans ce genre là :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- ===========
-- Paramétrage
-- ===========
 
SET NOCOUNT ON
 
-- ==================
-- Lien vers Database
-- ==================
 
use tempdb
 
Le contexte de la base de données a changé*; il est maintenant 'tempdb'.
 
SET DATEFORMAT mdy
 
SELECT TRY_CAST('12/31/2010' AS datetime2) AS Result
 
Result
--------------------------------------
           2010-12-31 00:00:00.0000000
 
SELECT TRY_CAST('29/02/2013' AS datetime2) AS Result
 
Result
--------------------------------------
                                  NULL
 
Appuyez sur une touche pour continuer...

A ma connaissance, on n'a pas l'équivalent sous MySql.

C'est très bien, sauf que cela dépend du contexte où l'on se trouve.
Dans un select, il n'y a pas d'erreur mais dans le chargement de la date, l'erreur se produit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
--------------
select str_to_date("54/11/2018", '%d/%m/%Y')
--------------
 
+---------------------------------------+
| str_to_date("54/11/2018", '%d/%m/%Y') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
 
Appuyez sur une touche pour continuer...

Cette erreur est voulue par MySql :
--> https://dev.mysql.com/doc/refman/8.0...on_str-to-date

If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.

@+

**Artemus24** · 25/09/2018, 12h19

Salut Delioneras.

Dans ce cas, au moment du chargement de la date dans la table, n'utilisez pas le type date, comme je l'ai fait, mais le type char(10).
Cela vous évitera d'avoir une erreur, mais ce n'est pas propre du tout, sauf si votre table est une table de travail comme dans mon exemple.

@+

**Delioneras** · 25/09/2018, 12h29

Lors de la création de la table, le champ est bien précisé au format VARCHAR
dms_date_mes VARCHAR(15) NOT NULL COMMENT 'date mes format AAAAMMJJ (texte)',

Et il en est de même lors de l'insertion dans cette table.

C'est vraiment lors du SELECT que l'exception est levée. Enfin non, c'est au moment d'une insertion dans une autre table, mais là aussi ce sont des champs textuels.
crl_id int(10) unsigned (NULL) NO PRI (NULL) auto_increment select,insert,update,references
crl_param1 mediumtext utf8_bin YES (NULL) select,insert,update,references
crl_param2 mediumtext utf8_bin YES (NULL) select,insert,update,references

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
INSERT INTO COMPTE_RENDU (crl_param1, crl_param2)
SELECT dms.dms_numero AS param1, dms.dms_date_mes AS param2
FROM tmp_maj_dms AS dms
WHERE DATE(dms.dms_date_mes) IS NULL
OR dms.dms_date_mes NOT RLIKE '[0-9]{8}'

Et c'est la clause DATE(dms.dms_date_mes) IS NULL qui lève une exception.

Ceci dit, dans ma table COMPTE_RENDU, j'ai bien les informations que je souhaite.

**Artemus24** · 25/09/2018, 12h43

Salut Delioneras.

Envoyé par Delioneras

Et c'est la clause DATE(dms.dms_date_mes) IS NULL qui lève une exception.

Pour tester si la colonne est à NULL, vous n'avez pas besoin de la convertir dans le type DATE.

Dans ma fonction, c'est exactement la même chose :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

return str_to_date(in_date, '%d/%m/%Y');

produit un erreur car il y a conversion dans le type date.

Si je ne désire pas passer par cette fonction "str_to_date", je suis obligé de faire moi-même la conversion :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `trav`
--------------
 
--------------
CREATE TABLE `trav`
( `id`    integer unsigned   not null auto_increment primary key,
  `date`  char(10)           null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
CREATE FUNCTION chgdate(in_date text) RETURNS char(10)
  DETERMINISTIC
  LANGUAGE SQL
BEGIN
  return concat(substring_index(in_date,'/',-1),'-',substring_index(substring_index(in_date,'/',2),'/',-1),'-',substring_index(in_date,'/',1));
END
--------------
 
--------------
LOAD DATA INFILE 'E:/30.MySql/40.Load/Ex_07/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1,@F2)
    SET `date`= chgdate(@F2)
--------------
 
--------------
select * from trav
--------------
 
+----+------------+
| id | date       |
+----+------------+
|  1 | NULL       |
|  2 | 2018-07-12 |
|  3 | 2018-09-54 |
|  4 | 2018-19-04 |
|  5 | 9999-11-04 |
|  6 | 2017-04-31 |
|  7 | 2018-10-27 |
|  8 | 2013-02-29 |
+----+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 0
--------------
 
Appuyez sur une touche pour continuer...

@+

**Artemus24** · 25/09/2018, 12h50

Salut Delioneras.

Pardon, je viens de comprendre que "WHERE DATE(dms.dms_date_mes) IS NULL" vous permet de tester la validité de la date.

Vous n'avez pas le droit aux procédures stockées, aux fonctions aux triggers, et aux view et vous désirez trouver une astuce pour tester la validité d'une date sans produire une erreur.

Or ce que je ne comprends pas chez vous, c'est l'erreur sur le select :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
--------------
select *
from trav
where date(`date`) is null
--------------
 
+----+------------+
| id | date       |
+----+------------+
|  1 | NULL       |
|  3 | 2018-09-54 |
|  4 | 2018-19-04 |
|  6 | 2017-04-31 |
|  8 | 2013-02-29 |
+----+------------+
--------------
select *
from trav
where date(`date`) is not null
--------------
 
+----+------------+
| id | date       |
+----+------------+
|  2 | 2018-07-12 |
|  5 | 9999-11-04 |
|  7 | 2018-10-27 |
+----+------------+
 
Appuyez sur une touche pour continuer...

Dans cet exemple, la colonne `date` est dans le type "char(10)" et comme vous le constatez, il n'y aucune erreur chez moi.

Juste une petite question, est-ce bien du mysql dont vous parlez ?

@+

**Artemus24** · 25/09/2018, 15h52

Salut Delioneras.

J'ai reproduit votre problème et je l'ai résolu !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `trav`
--------------
 
--------------
CREATE TABLE `trav`
( `id`    integer unsigned   not null auto_increment primary key,
  `date`  char(10)               null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA INFILE 'E:/30.MySql/40.Load/Ex_07/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1,@F2)
    SET `date`= concat(substring_index(@F2,'/',-1),'-',substring_index(substring_index(@F2,'/',2),'/',-1),'-',substring_index(@F2,'/',1))
--------------
 
--------------
select * from trav
--------------
 
+----+------------+
| id | date       |
+----+------------+
|  1 | NULL       |
|  2 | 2018-07-12 |
|  3 | 2018-09-54 |
|  4 | 2018-19-04 |
|  5 | 9999-11-04 |
|  6 | 2017-04-31 |
|  7 | 2018-10-27 |
|  8 | 2013-02-29 |
+----+------------+
--------------
DROP TABLE IF EXISTS `compte_rendu`
--------------
 
--------------
CREATE TABLE `compte_rendu`
( `id`          integer unsigned   not null auto_increment primary key,
  `crl_param1`  varchar(255)       not null,
  `crl_param2`     char(10)            null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT IGNORE INTO `COMPTE_RENDU` (crl_param1, crl_param2)
  SELECT  id     AS crl_param1,
          `date` AS crl_param2
    FROM  `trav`
   WHERE  DATE(`date`) IS NULL
--------------
 
--------------
select * from `compte_rendu`
--------------
 
+----+------------+------------+
| id | crl_param1 | crl_param2 |
+----+------------+------------+
|  1 | 1          | NULL       |
|  2 | 3          | 2018-09-54 |
|  3 | 4          | 2018-19-04 |
|  4 | 6          | 2017-04-31 |
|  5 | 8          | 2013-02-29 |
+----+------------+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 0
--------------
 
Appuyez sur une touche pour continuer...

Il suffisait de mettre "ignore" dans l'insertion et il n'y a plus de messages d'erreurs bloquants.
A vous de tester de votre coté et de me dire si cette solution vous convient.

@+

**Delioneras** · 25/09/2018, 16h44

Bonjour,

Le rajout d'IGNORE dans la clause INSERT m'avait inséré le corps de la requête dans mon CR plutôt que les valeurs recherchées.

J'ai réglé le problème à un niveau bien en amont, juste avant insertion réelle dans la base, via une vérif en PHP.

Encore merci à vous deux,

Ne pas déclencher de warning sur une query Insert [MySQL-5.5]

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager