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?