Discussion :

[Artemus24]

Salut à tous.

Code mysql :

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
Heure Début :  0:52:38,61
--------------
START TRANSACTION
--------------
 
--------------
SHOW SESSION VARIABLES LIKE '%timeout'
--------------
 
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 900      |
| delayed_insert_timeout      | 900      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 900      |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 900      |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 900      |
| net_write_timeout           | 900      |
| replica_net_timeout         | 900      |
| rpl_stop_replica_timeout    | 31536000 |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 900      |
| ssl_session_cache_timeout   | 300      |
| wait_timeout                | 900      |
+-----------------------------+----------+
--------------
ALTER TABLE charniere ADD CONSTRAINT `FK_01` FOREIGN KEY (`medecin`) REFERENCES `medecin` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
--------------
 
ERROR 2013 (HY000) at line 10 in file: 'E:\30.MySql\20.Basic\21.Optimisation\Ex_02\Prog\05.Base.sql': Lost connection to MySQL server during query
--------------
ALTER TABLE charniere ADD CONSTRAINT `FK_02` FOREIGN KEY (`patient`) REFERENCES `patient` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
--------------
 
ERROR 2013 (HY000) at line 11 in file: 'E:\30.MySql\20.Basic\21.Optimisation\Ex_02\Prog\05.Base.sql': Lost connection to MySQL server during query
--------------
COMMIT
--------------
 
ERROR 2013 (HY000) at line 17 in file: 'E:\30.MySql\20.Basic\21.Optimisation\Ex_02\Prog\05.Base.sql': Lost connection to MySQL server during query
Heure Fin ..:  0:52:50,71

Le traitement dure à peine 12,1 secondes et je perds la connexion.
Normalement, j'ai paramétré le timeout à 15 Minutes (900 secondes). Mais a priori, ce n'est pas ce type de problème que je rencontre.
Les deux tables ont 1 millions de lignes. La première fait 24Mo et la seconde 40Mo.
Je ne comprends pas l'origine de ce problème.

J'ai fait des recherches sur le net et je tombe fréquemment sur le timeout, ou sur "max_allowed_packet", mais ce n'est pas ça.
Quel serait l'origine de ce problème ?

Cordialement.
Artemus24.
@+

[Séb.]

C'est la création de la clef étrangère qui provoque cela ?
Le serveur MySQL plante où est-ce juste la connexion au serveur qui tombe ?
La requête continue-t-elle son exécution un temps après l'erreur ?

Si tu peux transmettre le jeu de données je suis preneur pour tester de mon côté.

[Artemus24]

Salut Seb..

C'est la création de la clef étrangère qui provoque cela ?

Oui, à la création de deux clefs étrangères.

Le serveur MySQL plante où est-ce juste la connexion au serveur qui tombe ?

Le serveur est encore opérationnel après cela, et je n'ai aucun message dans "MySql_error.log".
C'est seulement la connexion qui a disparu dans la session du script.

La requête continue-t-elle son exécution un temps après l'erreur ?

Tout se trouve dans le compte-rendu de l'exécution de mon premier message.
Je pense que l'erreur se produit sur la première création des clefs étrangères.
Puis ayant perdu la connexion, le script continue et va jusqu'au bout en indiquant à chaque fois la même erreur.

J'ai fait un recherche sur l'erreur 2013, mais je n'ai rien trouvé de probant.

Si tu peux transmettre le jeu de données je suis preneur pour tester de mon côté.

J'ai trouvé l'origine de l'erreur.
A l'origine, le script était dans un seul fichier et j'ai séparé le tout en cinq scripts plus court.
Sauf que j'ai oublié de modifier les lignes où je désactive les index et le mode transaction automatique.
Il y avait "SESSION" et quand j'ai modifié en mettant "GLOBAL", l'erreur à disparue.

Je ne comprends pas pourquoi cela a provoqué l'erreur 2013.

Cordialement.
Artemus24.
@+

[Artemus24]

Comme demandé, voici les scripts MySql :

Code mysql :

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
START TRANSACTION;
 
-- ===========
-- Paramétrage
-- ===========
 
ALTER INSTANCE DISABLE InnoDB REDO_LOG;
 
SET GLOBAL autocommit         = 0;
SET GLOBAL unique_checks      = 0;
SET GLOBAL foreign_key_checks = 0;
 
-- ======================
-- Base de Données `base`
-- ======================
 
DROP DATABASE IF EXISTS `base`;
 
CREATE DATABASE `base`
	DEFAULT CHARACTER SET `latin1`
	DEFAULT COLLATE       `latin1_general_ci`;
 
USE `base`;
 
-- ===
-- Fin
-- ===
 
COMMIT;
EXIT

Création de la table des médecins :

Code mysql :

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
START TRANSACTION;
USE `base`;
 
-- ===============
-- Table `medecin`
-- ===============
 
DROP TABLE IF EXISTS `medecin`;
 
CREATE TABLE `medecin`
( `id`          integer unsigned auto_increment NOT NULL primary key,
  `nom`         varchar(255)                    NOT NULL,
  `prenom`      varchar(255)                    NOT NULL,
  `mail`        varchar(255)                    NOT NULL,
  `specialite`  smallint unsigned               NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- =====================
-- Procédure `ajout_med`
-- =====================
 
DROP PROCEDURE IF EXISTS ajout_med;
 
DELIMITER $$
CREATE PROCEDURE ajout_med
(
	IN In_Qte   INTEGER UNSIGNED,
	IN In_Start INTEGER UNSIGNED,
	IN In_End   INTEGER UNSIGNED
)
DETERMINISTIC
NO SQL
BEGIN
  DECLARE _max  INTEGER UNSIGNED DEFAULT 100;
  DECLARE _ind1 INTEGER UNSIGNED DEFAULT 0;
  DECLARE _ind2 INTEGER UNSIGNED DEFAULT 0;
  DECLARE _nbr  INTEGER UNSIGNED DEFAULT 0;
 
  DECLARE _nom  VARCHAR(255)     DEFAULT NULL;
  DECLARE _pre  VARCHAR(255)     DEFAULT NULL;
  DECLARE _mail VARCHAR(255)     DEFAULT NULL;
  DECLARE _spe  INTEGER UNSIGNED DEFAULT 0;
 
  SET _nbr  = 1;
  SET _ind1 = floor(In_Qte / _max);
 
  ALTER TABLE `medecin` DISABLE KEYS;
 
  WHILE _ind1 > 0
  DO
    START TRANSACTION;
    SET _ind2 = _max;
 
    WHILE _ind2 > 0
    DO
      SET _nom  =         concat('nom',    right( concat('00000', _nbr), 5));
      SET _pre  =         concat('prenom', right( concat('00000', _nbr), 5));
      SET _mail = concat( concat('id',     right( concat('00000', _nbr), 5)), '@docteur.com');
      SET _spe  = round( cast(rand() * (In_End - In_Start) as unsigned) + In_Start);
 
      INSERT INTO `medecin` (`nom`,`prenom`,`mail`,`specialite`) VALUE (_nom, _pre, _mail, _spe);
 
      SET _ind2 = _ind2 - 1;
      SET _nbr  = _nbr  + 1;
    END WHILE;
 
    COMMIT;
    SET _ind1 = _ind1 - 1;
  END WHILE;
 
  ALTER TABLE `medecin` ENABLE KEYS;
END$$
DELIMITER ;
 
-- ========================
-- Remplissage de `medecin`
-- ========================
 
call ajout_med(4000, 1, 8);
 
-- ===================
-- Vidage de `medecin`
-- ===================
 
select count(*) from medecin;
 
select * from medecin limit 10;
 
-- ===
-- Fin
-- ===
 
COMMIT;
EXIT

Création de la table des patients :

Code mysql :

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
START TRANSACTION;
USE `base`;
 
-- ===============
-- Table `patient`
-- ===============
 
DROP TABLE IF EXISTS `patient`;
 
CREATE TABLE `patient`
( `id`      integer unsigned auto_increment NOT NULL primary key,
  `nom`     varchar(255)                    NOT NULL,
  `prenom`  varchar(255)                    NOT NULL,
  `mail`    varchar(255)                    NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- =====================
-- Procédure `ajout_pat`
-- =====================
 
DROP PROCEDURE IF EXISTS ajout_pat;
 
DELIMITER $$
CREATE PROCEDURE ajout_pat
(
	IN In_Qte   INTEGER UNSIGNED
)
DETERMINISTIC
NO SQL
BEGIN
  DECLARE _max  INTEGER UNSIGNED DEFAULT 100;
  DECLARE _ind1 INTEGER UNSIGNED DEFAULT 0;
  DECLARE _ind2 INTEGER UNSIGNED DEFAULT 0;
  DECLARE _nbr  INTEGER UNSIGNED DEFAULT 0;
 
  DECLARE _nom  VARCHAR(255)     DEFAULT NULL;
  DECLARE _pre  VARCHAR(255)     DEFAULT NULL;
  DECLARE _mail VARCHAR(255)     DEFAULT NULL;
 
  SET _nbr  = 1;
  SET _ind1 = floor(In_Qte / _max);
 
  ALTER TABLE `patient` DISABLE KEYS;
 
  WHILE _ind1 > 0
  DO
    START TRANSACTION;
    SET _ind2 = _max;
 
    WHILE _ind2 > 0
    DO
      SET _nom  =         concat('nom',    right( concat('0000000', _nbr), 7));
      SET _pre  =         concat('prenom', right( concat('0000000', _nbr), 7));
      SET _mail = concat( concat('id',     right( concat('0000000', _nbr), 7)), '@patient.com');
 
      INSERT INTO `patient` (`nom`,`prenom`,`mail`) VALUE (_nom, _pre, _mail);
 
      SET _ind2 = _ind2 - 1;
      SET _nbr  = _nbr  + 1;
    END WHILE;
 
    COMMIT;
    SET _ind1 = _ind1 - 1;
  END WHILE;
 
  ALTER TABLE `patient` ENABLE KEYS;
END$$
DELIMITER ;
 
-- ========================
-- Remplissage de `patient`
-- ========================
 
call ajout_pat(1000000);
 
-- ===================
-- Vidage de `patient`
-- ===================
 
select count(*) from patient;
 
select * from patient limit 10;
 
-- ===
-- Fin
-- ===
 
COMMIT;
EXIT

Création de la table charnières :

Code mysql :

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
START TRANSACTION;
USE `base`;
 
-- =================
-- Table `charniere`
-- =================
 
DROP TABLE IF EXISTS `charniere`;
 
CREATE TABLE `charniere`
( `id`       integer unsigned auto_increment NOT NULL primary key,
  `medecin`  integer unsigned                NOT NULL,
  `patient`  integer unsigned                NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- ======================
-- Procédure `ajout_char`
-- ======================
 
DROP PROCEDURE IF EXISTS ajout_char;
 
DELIMITER $$
CREATE PROCEDURE ajout_char
(
	IN In_Qte   INTEGER UNSIGNED,
	IN In_Start INTEGER UNSIGNED,
	IN In_End   INTEGER UNSIGNED
)
DETERMINISTIC
NO SQL
BEGIN
  DECLARE _max  INTEGER UNSIGNED DEFAULT 100;
  DECLARE _ind1 INTEGER UNSIGNED DEFAULT 0;
  DECLARE _ind2 INTEGER UNSIGNED DEFAULT 0;
  DECLARE _nbr  INTEGER UNSIGNED DEFAULT 0;
 
  DECLARE _med  INTEGER UNSIGNED DEFAULT 0;
 
  SET _nbr  = 1;
  SET _ind1 = floor(In_Qte / _max);
 
  ALTER TABLE `charniere` DISABLE KEYS;
 
  WHILE _ind1 > 0
  DO
    START TRANSACTION;
    SET _ind2 = _max;
 
    WHILE _ind2 > 0
    DO
      SET _med  = round( cast(rand() * (In_End - In_Start) as unsigned) + In_Start);
 
      INSERT INTO `charniere` (`medecin`,`patient`) VALUE (_med, _nbr);
      SET _ind2 = _ind2 - 1;
      SET _nbr  = _nbr  + 1;
    END WHILE;
 
    COMMIT;
    SET _ind1 = _ind1 - 1;
  END WHILE;
 
  ALTER TABLE `charniere` ENABLE KEYS;
END$$
DELIMITER ;
 
-- ==========================
-- Remplissage de `charniere`
-- ==========================
 
call ajout_char(1000000, 1, 4000);
 
-- =====================
-- Vidage de `charniere`
-- =====================
 
select count(*) from charniere;
 
select * from charniere limit 10;
 
-- ===
-- Fin
-- ===
 
COMMIT;
EXIT

La création des clefs étrangères :

Code mysql :

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
START TRANSACTION;
USE `base`;
 
-- ======================================
-- Création des contraintes et de l'index
-- ======================================
 
ALTER TABLE charniere ADD CONSTRAINT `FK_01` FOREIGN KEY (`medecin`) REFERENCES `medecin` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE charniere ADD CONSTRAINT `FK_02` FOREIGN KEY (`patient`) REFERENCES `patient` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
 
-- ===
-- Fin
-- ===
 
COMMIT;
EXIT

Et le final :

Code mysql :

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
START TRANSACTION;
USE `base`;
 
-- ===========
-- Paramétrage
-- ===========
 
SET GLOBAL foreign_key_checks = 1;
SET GLOBAL unique_checks      = 1;
SET GLOBAL autocommit         = 1;
 
ALTER INSTANCE ENABLE InnoDB REDO_LOG;
 
-- ============
-- Compte-rendu
-- ============
 
describe `medecin`;
describe `patient`;
describe `charniere`;
 
COMMIT;
EXIT

a) création de la base de données vide.
b) création de la table des médecins.
c) création de la table des patients.
d) création de la table charnière.
e) création des clefs étrangères.
f) remise en état du paramétrage et affichage de la description des tables.

Et voici le temps elaps de l'exécution de mes six scripts :

a) Heure Début : 16:50:42,22 / Heure Fin ..: 16:50:43,36 / Durée : 1,14 secondes.
b) Heure Début : 16:50:46,29 / Heure Fin ..: 16:50:47,57 / Durée : 1,28 secondes.
c) Heure Début : 16:50:50,60 / Heure Fin ..: 16:51:28,91 / Durée : 38,31 secondes.
d) Heure Début : 16:51:31,83 / Heure Fin ..: 16:52:06,69 / Durée : 34,86 secondes.
e) Heure Début : 16:52:10,00 / Heure Fin ..: 16:52:15,36 / Durée : 5,36 secondes.
f) Heure Début : 16:52:18,66 / Heure Fin ..: 16:52:19,88 / Durée : 1,22 secondes

Soit un total de 82,17 secondes, soit 1 minute 22,17 secondes.

Cordialement.
Artemus24.
@+

[Artemus24]

Salut à tous.

Je pensais avoir résolu mon problème mais cela recommence sur la requête suivante :

Code :

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

insert into new_charniere select * from charniere;

J'ai le même message d'erreur que le titre de ce sujet.

Si le temps d'exécution d'une requête dépasse les 10 secondes, elle se plante.
Le message "Lost connection to MySQL server during query" est trop vague pour déterminer la cause de l'erreur.
J'ai pourtant mis partout 900 secondes (=15 minutes) dans les variables contenant le mot "timeout".
(Voir mon premier message.)

J'ai passé la matinée à chercher sur le net mais je n'ai rien trouvé.
Il me semble que le problème vient de la connexion SSH, mais je n'en suis pas sûr.
C'est très proche de la même erreur concernant le workbench MySql, que je n'utilise pas.

J'ai pensé à mettre 1Go à la variable "max_allowed_packet", mais ce n'est pas ça.
Je pense que le problème concerne bien un timeout, mais je ne sais pas lesquel.

Cordialement.
Artemus24.
@+

[SQLpro]

Les joies de MySQmerde...

[binarygirl]

J'ai déjà eu des problèmes dans le passé avec des grosses copies de tables Innodb, des fichiers temporaires remplissaient le disque dur qui avait atteint sa capacité (c'était une petite VM...). De mémoire j'avais aussi ajusté innodb_buffer_pool (de mémoire ça devrait être un peu moins que la quantité de RAM disponible).

Il me semble que le problème vient de la connexion SSH, mais je n'en suis pas sûr.

Certes SSH est sensible aux microcoupures, mais je ne vois pas vraiment le rapport ici, car même si vous êtes connecté au serveur de DB en console via SSH il me semble que la copie de données se fait sur le serveur même et non via un transfert de données sur le réseau n'est ce pas ? Pouvez-vous clarifier si vous lancez ce script sur le serveur même ou via un client installé sur votre PC ?

J'ai pensé à mettre 1Go à la variable "max_allowed_packet", mais ce n'est pas ça.

Je pense effectivement que ce n'est pas là le problème.
Si je relis la doc: B.3.2.8 Packet Too Large
(les points intéressants soulignés par moi-même)

A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a replication source server to a replica.

The largest possible packet that can be transmitted to or from a MySQL 8.0 server or client is 1GB.

When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.

Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.

Si vous ne voyez rien dans les logs Mysql, regardez quand même dans /var/log/messages par exemple ou /var/log/syslog, au cas où il y aurait un manque de mémoire signalé ou autre chose. A la limite, faites tourner cette commande en root: journalctl -f dans une console avant d'exécuter le script.