{"id":1102,"date":"2011-08-29T15:51:32","date_gmt":"2011-08-29T14:51:32","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1102"},"modified":"2011-08-31T09:53:42","modified_gmt":"2011-08-31T08:53:42","slug":"duplicate-key-cause-par-un-select","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2011\/08\/29\/duplicate-key-cause-par-un-select\/","title":{"rendered":"Duplicate key caus\u00e9 par un SELECT"},"content":{"rendered":"

Parmi les erreurs courantes en MySQL, ont trouve la fameuse ERROR 1062 (23000): Duplicate entry ‘2984’ for key ‘PRIMARY’ caus\u00e9e par la tentative d’insertion d’une donn\u00e9es, d\u00e9j\u00e0 pr\u00e9sente, dans un colonne ayant une contrainte d’unicit\u00e9.<\/p>\n

Dans la m\u00eame famille il y a aussi l’erreur 1022…<\/p>\n

Une banale requ\u00eate de lecture avec un GROUP BY:<\/p>\n

mysql> SELECT DISTINCT(name), COUNT(name) FROM membre WHERE name <> \u00a0\u00bb GROUP BY name<\/address>\n
ERROR 1022 (23000): Can’t write; duplicate key in table ‘\/tmp\/#sql_29lm_0’<\/strong><\/address>\n
\u00a0<\/address>\n

C’est assez surprenant de trouver une telle erreur sur une requ\u00eate aussi triviale !<\/p>\n

En regardant le plan d’ex\u00e9cution, on remarque que outre le fait qu’ un index est plus que recommand\u00e9, MySQL cr\u00e9\u00e9 une table temporaire (\/tmp\/#sql_29lm_0) et c’est en ins\u00e9rant les donn\u00e9es dans cette derni\u00e8re que le \u00ab\u00a0duplicate key\u00a0\u00bb se d\u00e9clenche.<\/p>\n

\u00a0<\/address>\n
mysql> explain\u00a0 SELECT DISTINCT(name), COUNT(name) FROM membre WHERE name <> \u00a0\u00bb GROUP BY name\\G
\n*************************** 1. row ***************************
\nid: 1
\nselect_type: SIMPLE
\ntable: membre
\ntype: ALL
\npossible_keys: NULL
\nkey: NULL
\nkey_len: NULL
\nref: NULL
\nrows: 92633696
\nExtra: Using where; Using temporary<\/strong>; Using filesort<\/address>\n
\u00a0<\/address>\n
Alors \u00e7a \u00e0 tout l’air d’un bug, d’ailleurs 3 sont ouverts \u00e0 ce sujet, pour les version 5.1 et 5.5 (le 1er depuis le 9 Nov 2010…):<\/address>\n

http:\/\/bugs.mysql.com\/bug.php?id=58081<\/a>
\nhttp:\/\/bugs.mysql.com\/bug.php?id=60808<\/a>
\nhttp:\/\/bugs.mysql.com\/bug.php?id=62097<\/a><\/p>\n

Le contournement que j’ai trouv\u00e9 \u00e0 \u00e9t\u00e9 d’augmenter pour la session, les caches utilis\u00e9s par la requ\u00eates, \u00e0 savoir le max_heap_table_size<\/strong> et le tmp_table_size<\/strong>:<\/p>\n

mysql> SET SESSION max_heap_table_size=536870912; SET SESSION tmp_table_size=536870912;<\/address>\n
mysql> SELECT DISTINCT(name), COUNT(name) FROM membre WHERE name <> \u00a0\u00bb GROUP BY name<\/address>\n
+—————+————-+<\/address>\n
| name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | COUNT(name) |<\/address>\n
+—————+————-+<\/address>\n
| \u00a0\u00a0 \u00a0Abano\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 |<\/address>\n
…<\/address>\n
\u00a0<\/address>\n

Cela dit, \u00e7a serait quand m\u00eame pas mal, qu’ Oracle fasse le n\u00e9cessaire pour enfin corriger ce bug…<\/p>\n","protected":false},"excerpt":{"rendered":"

Parmi les erreurs courantes en MySQL, ont trouve la fameuse ERROR 1062 (23000): Duplicate entry ‘2984’ for key ‘PRIMARY’ caus\u00e9e par la tentative d’insertion d’une donn\u00e9es, d\u00e9j\u00e0 pr\u00e9sente, dans un colonne ayant une contrainte d’unicit\u00e9.<\/p>\n

Dans la m\u00eame famille il y a aussi l’erreur 1022…<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[88,8],"tags":[175,176],"class_list":["post-1102","post","type-post","status-publish","format-standard","hentry","category-astuce","category-mysql","tag-duplicate-key","tag-error-1022"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-hM","jetpack-related-posts":[{"id":1261,"url":"https:\/\/dasini.net\/blog\/2012\/05\/30\/optimiser-un-alter\/","url_meta":{"origin":1102,"position":0},"title":"Optimiser un ALTER","author":"Olivier DASINI","date":"30 mai 2012","format":false,"excerpt":"Lors d'une discussion \u00e0 la caf\u00e8t, la question suivante fut pos\u00e9e : \u00ab Faire un ALTER TABLE avec plusieurs instructions est il plus rapide qu'un ALTER TABLE par instruction \u00bb ? Les 2 protagonistes n'\u00e9tant pas d'accord entre eux, c'est tout naturellement que je fus invit\u00e9 \u00e0 donner mon avis.\u2026","rel":"","context":"Dans "Astuce"","block_context":{"text":"Astuce","link":"https:\/\/dasini.net\/blog\/category\/astuce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1533,"url":"https:\/\/dasini.net\/blog\/2015\/11\/17\/30-mins-avec-json-en-mysql\/","url_meta":{"origin":1102,"position":1},"title":"30 mins avec JSON en MySQL","author":"Olivier DASINI","date":"17 novembre 2015","format":false,"excerpt":"Comme vous le savez MySQL 5.7 est GA. Cette nouvelle mouture de la base de donn\u00e9es open source la plus populaire au monde a plus de 150 nouvelles fonctionnalit\u00e9s. L'une d'entre elle est un type de donn\u00e9es JSON natif ainsi que les fonctions JSON associ\u00e9es. Prenons 30 minutes pour voir\u2026","rel":"","context":"Dans "json"","block_context":{"text":"json","link":"https:\/\/dasini.net\/blog\/category\/json-fr\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1163,"url":"https:\/\/dasini.net\/blog\/2012\/01\/09\/ameliorations-de-loptimiseur-dans-mariadb\/","url_meta":{"origin":1102,"position":2},"title":"Am\u00e9liorations de l’optimiseur dans MariaDB","author":"Olivier DASINI","date":"9 janvier 2012","format":false,"excerpt":"Les \u00e9quipes de MariaDB ont \u00e9norm\u00e9ment travaill\u00e9es sur l'optimiseur de la version 5.3, notamment en permettant une r\u00e9elle utilisation des sous-requ\u00eates. Voici un effet visuel de ces optimisations: Avec MySQL 5.5, l'utilisation de tables d\u00e9riv\u00e9es (type de sous-requ\u00eates dans la clause FROM d'un SELECT), donne le plan d'ex\u00e9cution suivant:","rel":"","context":"Dans "bench"","block_context":{"text":"bench","link":"https:\/\/dasini.net\/blog\/category\/bench\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":56,"url":"https:\/\/dasini.net\/blog\/2008\/11\/02\/auto_increment-differences-myisam-innodb\/","url_meta":{"origin":1102,"position":3},"title":"AUTO_INCREMENT: Diff\u00e9rences MyISAM – InnoDB","author":"Olivier DASINI","date":"2 novembre 2008","format":false,"excerpt":"La clause, AUTO_INCREMENT, permet \u00e0 MySQL de g\u00e9n\u00e9rer un entier unique pour tout nouvel enregistrement d'une table. Cette clause ne peut se mettre que sur les champs de type entier, index\u00e9 et non nul. Elle est donc souvent utilis\u00e9e comme cl\u00e9 primaire. Cependant, sont comportement n'est pas tout \u00e0 fait\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":453,"url":"https:\/\/dasini.net\/blog\/2009\/02\/18\/optimisation-de-requetes-comprendre-loptimiseur-de-mysql\/","url_meta":{"origin":1102,"position":4},"title":"Optimisation de requ\u00eates: comprendre l’optimiseur de MySQL","author":"Olivier DASINI","date":"18 f\u00e9vrier 2009","format":false,"excerpt":"Le but de cet article est d'optimiser une simple requ\u00eate (SELECT avg(Population) FROM city GROUP BY CountryCode) et surtout de comprendre comment l'optimiseur proc\u00e8de, en \u00e9tudiant les r\u00e9sultats donn\u00e9s par les variables qui permettent de surveiller le serveur MySQL.","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1557,"url":"https:\/\/dasini.net\/blog\/2015\/11\/30\/json-et-colonnes-generees-avec-mysql\/","url_meta":{"origin":1102,"position":5},"title":"JSON et colonnes g\u00e9n\u00e9r\u00e9es avec MySQL","author":"Olivier DASINI","date":"30 novembre 2015","format":false,"excerpt":"Le 24 novembre dernier, lors du Forum PHP, Tomas Ulin (Oracle's MySQL VP of Engineering) a parl\u00e9 de l'utilisation de JSON dans MySQL \"MySQL 5.7 & JSON: New opportunities for developers\". Voici les r\u00e9ponses \u00e0 quelques questions qui m'ont \u00e9t\u00e9 pos\u00e9es: Comment se comporte mysqldump avec les colonnes g\u00e9n\u00e9r\u00e9es ?\u2026","rel":"","context":"Dans "json"","block_context":{"text":"json","link":"https:\/\/dasini.net\/blog\/category\/json-fr\/"},"img":{"alt_text":"JSON","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/json_icon.png?resize=350%2C200","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1102","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=1102"}],"version-history":[{"count":6,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1102\/revisions"}],"predecessor-version":[{"id":1106,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1102\/revisions\/1106"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1102"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1102"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1102"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}