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

I am doing user registration for a node application starting with this example, but using a MySQL database instead of MongoDB. Multiple columns in the users table have a unique 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 a user is on their profile page and tries to update the facebook_id in their existing user post, the update may violate the uniqueness constraint - that is, another user post already has a facebook_id .

MySQL error message reflected in Heroku log:

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


(I replaced x for the actual facebook_id.)

I want to do nothing but go back to the page the user is already on (their profile page) and display the error message "Facebook ID registered for another user." I am trying to use the connect-flash module to display a message. This works elsewhere in the program.

I am trying to accomplish it like this:

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 works with other .ejs pages.

On ER_DUP_ENTRY error, both console.log statements shown above are logged, so the program successfully catches the ER_DUP_ENTRY error, but instead of returning to the profile.ejs page as I was hoping, the profile page disappears and a lot of text is displayed in the browser, starting from:

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 homepage URL in the browser and everything works as usual.

Any idea what's going wrong? How can I handle this error so that the user just sees the link message on the profile page?


source to share

1 answer

maybe its 3 years late, but i leave the answer, better late than never!

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

        req.flash('message','The entry already exist.'); //we send the flash msg
        return res.redirect('/admin/userPanel');
            throw err;


errno is the property you are looking for, I got it when I was typing err to console using console.log (err).

You can check all error numbers here

It was fun! Good luck to the next kind soul who read this.



All Articles