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
maybe its 3 years late, but i leave 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();
}
}
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 https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html
It was fun! Good luck to the next kind soul who read this.
source to share