Problems with transaction

[AnderssonChristian]

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 };
    }
  };

[dougwilson]

Hi, sorry you are having trouble. Ultimately I don't think this module can possibly change the nature of Javascript itself and run both sections of the conditional. But I am happy to help debug. Can you include the DDL of the table and some sample data I can load it with that reproduces the issue you are experiencing?

[AnderssonChristian]

@dougwilson thanks for offering to help! It seems like the function is being called multiple times, and what I am seeing is simply the last run, which shows affectedRows: 0 since the row was deleted the first run.

As to why it's running multiple times, is beyond me. Let me just double-check and I'll update this post.

Thanks!

[dougwilson]

Ah, gotcha. Yea, the callback from your query should only happen once if the verifyEmail function itself is only called once based on the code. I would love to make sure that isn't the case if you have a way to reproduce. I just spent some time making up a table good enough to run the query and ran though the two conditions and didn't see any duplicate calls or other issues, but I may just not have the right data.

[AnderssonChristian]

@dougwilson Again, thanks for your readiness to help.

I started backtracking the code that calls this function and removing bit for bit. Thought I found the issue as I didn't get the error. Look into the removed code. Can't find anything. Undo the delete and try again - the code is now as it was originally. Works!?

Not sure if something was cached or what not, but everything seems to work now. Been pulling my hair for the better part of 3 hours.

PS: Is the approach for this transaction fine? Seems like an awful lot of code to just do a DELETE and UPDATE query. Just wanna make sure.