Open
Description
I am running an express server where I am trying to execute a somewhat complex transaction. I am getting the expected changes in my database, but not the expected output from the function.
I am getting the following response from my DELETE query:
{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"","serverStatus":3,"warningStatus":0}
What is weird is that, despite getting affectedRows: 0, it still runs the UPDATE query below. How is this possible?
Also, why am I getting status code 3? Is my transaction not written correctly?
Here's the full code:
verifyEmail = async ({ email, hash }) => {
try {
return await new Promise((resolve, reject) => {
db.getConnection((_err, connection) => {
connection.beginTransaction((err) => {
if (err) {
return reject({ error: err });
}
connection.query(
`DELETE FROM hashes WHERE user_id = (SELECT id FROM user WHERE email = ?) AND hash = ?`,
[email, hash],
(e1, res) => {
if (e1) {
connection.rollback(() => {
connection.release();
return reject({ error: e1 });
});
} else if (res.affectedRows === 0) {
connection.rollback(() => {
connection.release();
return reject(0); // <---- This is what I can see returned. When checked, I got the output posted above.
});
} else if (res.affectedRows === 1) {
// However this also seems to run?
connection.query(
`UPDATE user SET email_verified = 1 WHERE email = ?`,
[email],
(e2) => {
if (e2) {
connection.rollback(() => {
connection.release();
return reject({ error: e2 });
});
}
connection.commit((e3) => {
if (e3) {
connection.rollback(() => {
connection.release();
return reject({ error: e3 });
});
}
connection.release();
return resolve(1);
});
}
);
}
}
);
});
});
});
} catch (error) {
return { error };
}
};