Catch/Handle MySQL Duplicate Entry Error - with NodeJS, PassportJS, Express, connect-flash, Heroku

Question

I am doing user registration for a node app, starting from this example, but using a MySQL database instead of MongoDB. Multiple columns in the users table have a uniqueness constraint.

Here is the SQL code that creates the table:

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) \
)');

When the user is on their profile page and attempts to update the facebook_id in their existing user record, the update may violate the uniqueness constraint -- i.e. another user record already has that facebook_id.

The MySQL error message reflected in the Heroku log is:

Error: ER_DUP_ENTRY: Duplicate entry 'xxxxxxxxxxxxxxxxx' for key 'facebook_id_UNIQUE'

(I have substituted x's for the actual facebook_id.)

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 connect-flash module to display the message. This is working in other places in the program.

I am attempting to accomplish this as follows:

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 }

On the profile.ejs page, I have the following:

<% if (message.length > 0) { %>
    <div class="alert alert-danger"><%= message %></div>
<% } %>

This code is working on the other .ejs pages.

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:

Error: ER_DUP_ENTRY: Duplicate entry 'xxxxxxxxxxxxxxxxx' for key 'facebook_id_UNIQUE' at Query.Sequence._packetToError 

The app doesn't crash, the user can still enter the home page URL in the browser and things are working as usual.

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?

Answer 1

maybe its 3 years late but I'm leaving the answer, better late than never!.

   if(err){  //we make sure theres an error (error obj)

        if(err.errno==1062){   
        req.flash('message','The entry already exist.'); //we send the flash msg
        return res.redirect('/admin/userPanel');
        db.end();
        }
        else{
            throw err;
        db.end();
        }
}

errorno is the property that you are looking for, I got it when I printed err to console using console.log(err).

Here you can check all the errors number https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

it was a pleasure! Good luck to the next good soul that read this.

Answer 2

pretty straight and clear solution: //----------------

connection.query('write your mysql query', function(err,rows){
      if(err)
      {

        if(err.code == 'ER_DUP_ENTRY' || err.errno == 1062)
        {
            console.log('Here you can handle duplication')
        }
        else{
           console.log('Other error in the query')
        }

      }else{
         console.log('No error in the query')
      }
 })