MySQL : Autoriser/Forcer id=0 pour les PRIMARY KEY avec AUTO_INCREMENT

Dans le cadre de la migration des données d'une structure de base à une autre, j'ai été confronté à un petit problème. En effet, dans l'ancienne version de la BDD, certaines tables de paramètres ont un id démarrant à 0 au lieu de 1.

Exemple :

Table "civilite"
Id Valeur
0 Non communiqué
1 Féminin
2 Masculin

 

Or, lorsque je lance la requête :

INSERT INTO `civilite_new` (`civilite_new_id`, `civilite_new_label`)
SELECT `civilite_id`, `civilite_label` FROM `civilite`;

MySQL me retourne l'erreur suivante :

Error Code: 1062. Duplicate entry '1' for key 'PRIMARY'

L'AUTO_INCREMENT prend la première ligne (Id = 0) et force l'Id à 1. Du coup, lors du traitement de la deuxième ligne, MySQL bloque car la valeur de l'Id de cette dernière est également 1. Partant de ce constat, deux solutions sont possibles :

  • ré-incrémenter toutes les tables en décalant les id (on aurait donc 1, 2, 3 à la place de 0, 1, 2) - Attention aux intégrités référentielles
  • forcer la valeur 0 du premier Id - pas très "propre" mais efficace

Dans mon cas, la première solution ne me convient pas car le code de l'application qui alimente cette base de données repose sur le principe de Id = 0 pour "Non communiqué".

Donc, pour forcer la mise à 0 des premiers Id, il va donc falloir user du mode SQL NO_AUTO_VALUE_ON_ZERO :

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

Ajoutez la ligne ci-dessus avant votre INSERT ou UPDATE.

Une fois vos requêtes exécutées, n'oubliez pas de désactiver le mode "NO_AUTO_VALUE_ON_ZERO" :

SET SQL_MODE=@OLD_SQL_MODE;

Et le tour est joué ! ^^

MySQL : Désactiver temporairement les contraintes référentielles

Actuellement, je travaille sur un script PHP de migration de données d'une structure de base de données à une autre. L'ancienne structure était composée de table au format MyISAM sans contraintes référentielles (vu que ce format ne gère pas la déclaration de FOREIGN KEY). La nouvelle structure, quant à elle, est composée de table au format InnoDB où les intégrités référentielles sont déclarées.

Le problème avec ces contraintes référentielles, c'est que mon script de migration bloque dès qu'une de ces contraintes n'est pas respectée. Je vous l'accorde, ce n'est pas un problème en soi puisque c'est justement le but lorsqu'on déclare les intégrités référentielles : vérifier la cohérence du contenu de la base de données.

Une fois que le script m'a renvoyé l'erreur, je le corrige et il me faut alors vider la table impactée afin de le relancer et vérifier que le problème est bien résolu. Si je tente un TRUNCATE ou un DELETE FROM sur ma table, MySQL me renvoie alors l'erreur suivante :

Error Code: 1701 Cannot truncate a table referenced in a foreign key constraint (...)

Bien. MySQL vérifie donc correctement les contraintes. Cependant, il faut bien que je vide ma table pour relancer mon script. Pour pallier à ce problème, il suffit donc de suspendre le contrôle des contraintes temporairement (le temps de la requête en fait) :

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM bdd.table;     -- ou autre(s) requête(s)
SET FOREIGN_KEY_CHECKS=1;

Et le tour est joué ! ^^

Wamp : Configurer le stockage de configuration de phpMyAdmin

Edit du 27/11/2014 : J'ai apporté quelques corrections à ce billet car dans la première version, je créais l'utilisateur pma avec des droits sur toutes les bases de données. Or, d'un point de vue "sécurité", il vaut mieux que l'utilisateur pma n'est de droits que sur la table phpmyadmin.

Au quotidien, j'utilise MySQL Workbench pour l'administration de mes bases MySQL. Cependant, il est toujours utile d'avoir phpMyAdmin sous la main pour certaines opérations.

Si comme moi vous avez installé WampServer, vous avez surement remarqué ce message indiquant que vos préférences ne peuvent être enregistrées tant que vous n'avez pas configuré le "stockage de configurations phpMyAdmin".

1. Pour ce faire, il faut tout d'abord créer la base de données qui permettra de sauvegarder vos paramètres. Importez le fichier create_tables.sql (par défaut : C:\wamp\apps\phpmyadmin4.1.14\examples\create_tables.sql - bien entendu, le chemin varie selon votre version de phpMyAdmin) via l'onglet "Importer".

2. Il faut ensuite créer un utilisateur pour la base créée. Dans la liste des bases de données, cliquez sur la base phpmyadmin. Rendez vous ensuite dans l'onglet "Privilèges" et cliquez sur "Ajouter un utilisateur" :

  • Nom d'utilisateur : pma
  • Client : Local : localhost
  • Mot de passe : généré

Vérifiez bien que la case Donner tous les privilèges sur la base de données "phpmyadmin" est cochée.

3. Enfin, modifiez le fichier de configuration (C:\wamp\apps\phpmyadmin4.1.14\config.inc.php) en y ajoutant :

/* User used to manipulate with storage */
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'votreMotDePasse';

/* Storage database and tables */
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
$cfg['Servers'][$i]['users'] = 'pma__users';
$cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
$cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';

Et voilà, le tour est joué ! ^^

Vous pouvez désormais configurer phpMyAdmin à votre guise.

Mémo : Modifier le mot de passe root de MySQL dans Wamp

Si vous installer ou réinstaller Wamp (Apache - MySQL - PHP), vous avez sans doute constaté que, par défaut, l'utilisateur "root" de PhpMyAdmin (et donc de MySQL) n'a pas de mot de passe défini.

 

Pour y remédier, connectez-vous à PhpMyAdmin et modifiez le mot de passe :

  • cliquez sur la rubrique "Utilisateurs" du menu supérieur
  • cliquez sur "Changer les privilèges" de l'utilisateur "root", client "localhost"
  • modifiez le mot de passe dans la rubrique "Modifier le mot de passe" puis cliquez sur le bouton "Exécuter"

 

 

PhpMyAdmin va alors vous afficher un message d'erreur de connexion. Pour pouvoir vous reconnecter, il vous faut alors modifier le fichier C:/wamp/apps/phpmyadminX.X.X/config.inc.php.

Ajouter le nouveau mot de passe "root" à cette ligne :


$cfg['Servers'][$i]['password'] = 'votremotdepasse';

Pour finir, redémarrez les services.

 

Source : http://www.it-connect.fr/modifier-le-mot-de-passe-root-de-wamp%EF%BB%BF/