title: Catch/Handle MySQL Duplicate Entry Error - with NodeJS, PassportJS, Express, connect-flash, Heroku
body: <p>I am doing user registration for a node app, starting from <a href="http://scotch.io/tutorials/javascript/easy-node-authentication-setup-and-local" rel="noreferrer">this</a> example, but using a MySQL database instead of MongoDB. Multiple columns in the <strong>users</strong> table have a uniqueness constraint. </p>
  
  <p>Here is the SQL code that creates the table:</p>
  
  <pre><code>CREATE TABLE `' + dbconfig.database + '`.`' + dbconfig.users_table + '` ( \
      `id`                  INT UNSIGNED NOT NULL  AUTO_INCREMENT, \
      `username`            VARCHAR(60)  NULL, \
      `password`            CHAR(60)     NULL, \
      `facebook_id`         VARCHAR(60)  NULL, \
      `facebook_token`      CHAR(223)    NULL, \
      `facebook_name`       VARCHAR(100) NULL, \
      `facebook_email`      VARCHAR(100) NULL, \
      `google_id`           VARCHAR(60)  NULL, \
      `google_token`        CHAR(67)     NULL, \
      `google_name`         VARCHAR(100) NULL, \
      `google_email`        VARCHAR(100) NULL, \
      PRIMARY KEY (`id`), \
      UNIQUE INDEX `id_UNIQUE` (`id` ASC), \
      UNIQUE INDEX `username_UNIQUE` (`username` ASC), \
      UNIQUE INDEX `facebook_id_UNIQUE` (`facebook_id` ASC), \
      UNIQUE INDEX `facebook_token_UNIQUE` (`facebook_token` ASC), \
      UNIQUE INDEX `google_id_UNIQUE` (`google_id` ASC), \
      UNIQUE INDEX `google_token_UNIQUE` (`google_token` ASC) \
  )');
  </code></pre>
  
  <p>When the user is on their profile page and attempts to update the <strong>facebook_id</strong> in their existing user record, the update may violate the uniqueness constraint -- i.e. another user record already has that <strong>facebook_id</strong>.</p>
  
  <p>The MySQL error message reflected in the Heroku log is:</p>
  
  <pre><code>Error: ER_DUP_ENTRY: Duplicate entry 'xxxxxxxxxxxxxxxxx' for key 'facebook_id_UNIQUE'
  </code></pre>
  
  <p>(I have substituted x's for the actual facebook_id.)</p>
  
  <p>I want to do nothing except return to the page the user is already on (their profile page) and display an error message, "Facebook ID registered to another user." I am attempting to use the <a href="https://www.npmjs.org/package/connect-flash" rel="noreferrer">connect-flash</a> module to display the message. This is working in other places in the program.</p>
  
  <p>I am attempting to accomplish this as follows:</p>
  
  <pre><code>if (err) {
      console.log("mylog : facebook connect error");
      if (err.code === 'PROTOCOL_CONNECTION_LOST') {
          //handle disconnect
      } else if (err.code === 'ER_DUP_ENTRY') {
          console.log("mylog : fb ER_DUP_ENTRY detected");
          // release connection created with pool.getConnection
          if (connection) connection.release();
          // req.flash to set flashdata using connect-flash
          return done(err, false, req.flash('loginMessage', 'Facebook ID registered to another user.')); 
      } else { //continue }
  </code></pre>
  
  <p>On the profile.ejs page, I have the following:</p>
  
  <pre><code>&lt;% if (message.length &gt; 0) { %&gt;
      &lt;div class="alert alert-danger"&gt;&lt;%= message %&gt;&lt;/div&gt;
  &lt;% } %&gt;
  </code></pre>
  
  <p>This code is working on the other .ejs pages.</p>
  
  <p>In the case of the ER_DUP_ENTRY error, both console.log statements shown above are registering, so the program is detecting the ER_DUP_ENTRY error successfully, but instead of returning to the profile.ejs page as I hoped, the profile page disappears and and a lot of text is displayed in the browser, starting with:</p>
  
  <pre><code>Error: ER_DUP_ENTRY: Duplicate entry 'xxxxxxxxxxxxxxxxx' for key 'facebook_id_UNIQUE' at Query.Sequence._packetToError 
  </code></pre>
  
  <p>The app doesn't crash, the user can still enter the home page URL in the browser and things are working as usual.</p>
  
  <p>Any idea what is going wrong?  How can I handle this error so that the user just sees the connect-flash message on the profile page?</p>