Steps to retrieve the primary key associated with a duplicate entry error (1062) in MySQL using the Knex.js library

Question

Steps to retrieve the primary key associated with a duplicate entry error (1062) in MySQL using the Knex.js library

Testing out the database by sending values from an HTML form has been successful so far. Unique inserts and good connections are working well. However, intentionally sending a duplicate username for testing purposes results in an error message - which is exactly what I want!

The challenge now is to extract the cause of the error, which happens to be the key. The error object looks like this when checked with typeof(error):

Error: Insert into Users (password, role, username) values ('1112131234434', 'basic', 'Cthulu') - Duplicate entry 'Cthulu' for key 'username'

Attempting to extract the key after converting the object to JSON using JSON.stringify only gives part of the message: {"code":"ER_DUP_ENTRY","errno":1062,"sqlState":"#23000"} Missing is the other details needed. Is it not possible to convert the entire error to a string then perform a substring search for the key? Or am I approaching this problem incorrectly?

Additionally, trying to traverse through the keys of the object reveals that while it seems to be a string, it actually isn't. Extracting the specific part of the string as shown in "'key 'username'" from the error remains a challenge.

In essence, my aim is to send back the key 'username' to the client form indicating that their chosen username is already taken. The same process will later apply to the email, hence my need to identify and extract the key.

Edit: Considering implementing stored procedures with GET DIAGNOSTICS seems to be the next step. Following guidelines similar to this article might provide a solution.

javascript mysql angularjs json knex.js

Answer 1

Answer №1

It seems that I may not be the biggest fan of this solution, but it does work for me. What I did was create a stored procedure in MySQL using the GET DIAGNOSTICS command.

    DELIMITER //

    DROP PROCEDURE IF EXISTS `insert_new_user` //
    CREATE PROCEDURE `insert_new_user` (uname VARCHAR(45), pass CHAR(96), r varchar(15))
    BEGIN
        /**
        Return field that has triggered the error, otherwise, return good
        **/
     -- Declare variables to hold diagnostics area information
      DECLARE code CHAR(5) DEFAULT '00000';
      DECLARE msg TEXT;
      DECLARE rows INT;
      DECLARE result TEXT;
      -- Declare exception handler for failed insert
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
          GET DIAGNOSTICS CONDITION 1
            code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
        END;

      -- Perform the insert
      INSERT INTO Users(username, password, role) VALUES (uname, pass,  'full');

      -- Check whether the insert was successful
      IF code = '00000' THEN
        GET DIAGNOSTICS rows = ROW_COUNT;
        SET result = CONCAT('insert succeeded, row count = ',rows);
      ELSE
        SET result = CONCAT('insert failed, error = ',code,', message = ',msg);
      END IF;
      -- Display what happened
    SELECT result;

    END //

Although I modified the code slightly, I unfortunately can't recall the original source...

In my Express server, I utilized a knex.raw(...) statement. Then in the

.then(result => { ... })

function (since knex is a promise), I checked the result for errors by calling a getError(result) function. If an error is present, it provides me with the key, such as 'username'. (I still need to implement the alternative for no error) e.g.

---console output---for no errors

ERR_str: [[[{"result":"insert succeeded, row count = 1"}],{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"","serverStatus":2,"warning.....it goes on

---end output-------

For reference, here is the console output when error 1062 occurs:

---console output---for 1062 error

ERR_str: [[[{"result":"insert failed, error = 23000, message = Duplicate entry 'leerman2' for key 'username'"}],{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"","serverStatus":2,"warningStatus":0}],[[{"_b...it goes on

---end output-------

Below is the code for extracting the key:

    function getError(err) {
        const ERR_str = JSON.stringify(err);
        console.log(`Entered getError() function...proceeding to extract error...`);

        console.log(`ERR_str: ${ERR_str}`);
        console.log(`Error is string. [typeOf(ERR_str)]: ${typeof(ERR_str)}. (Attempting to extract key...`);
        const START = (ERR_str.indexOf('key')) + 'key'.length + 2; // 2 including spaces 
        console.log(`Start: ${START}`);
        const END = ERR_str.indexOf(`'"}`);
        console.log(`End: ${END}`);    
        const KEY = ERR_str.substr(START, END - START);
        console.log(`key to return to server: ${KEY}`);

        return KEY || 0;
    }

While not the most polished code, it serves as a solid starting point. I can now send it to Angular and continue working with it as intended. Any feedback on this approach is welcome.

Answer 2

It seems that I may not be the biggest fan of this solution, but it does work for me. What I did was create a stored procedure in MySQL using the GET DIAGNOSTICS command.

    DELIMITER //

    DROP PROCEDURE IF EXISTS `insert_new_user` //
    CREATE PROCEDURE `insert_new_user` (uname VARCHAR(45), pass CHAR(96), r varchar(15))
    BEGIN
        /**
        Return field that has triggered the error, otherwise, return good
        **/
     -- Declare variables to hold diagnostics area information
      DECLARE code CHAR(5) DEFAULT '00000';
      DECLARE msg TEXT;
      DECLARE rows INT;
      DECLARE result TEXT;
      -- Declare exception handler for failed insert
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
          GET DIAGNOSTICS CONDITION 1
            code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
        END;

      -- Perform the insert
      INSERT INTO Users(username, password, role) VALUES (uname, pass,  'full');

      -- Check whether the insert was successful
      IF code = '00000' THEN
        GET DIAGNOSTICS rows = ROW_COUNT;
        SET result = CONCAT('insert succeeded, row count = ',rows);
      ELSE
        SET result = CONCAT('insert failed, error = ',code,', message = ',msg);
      END IF;
      -- Display what happened
    SELECT result;

    END //

Although I modified the code slightly, I unfortunately can't recall the original source...

In my Express server, I utilized a knex.raw(...) statement. Then in the

.then(result => { ... })

function (since knex is a promise), I checked the result for errors by calling a getError(result) function. If an error is present, it provides me with the key, such as 'username'. (I still need to implement the alternative for no error) e.g.

---console output---for no errors

ERR_str: [[[{"result":"insert succeeded, row count = 1"}],{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"","serverStatus":2,"warning.....it goes on

---end output-------

For reference, here is the console output when error 1062 occurs:

---console output---for 1062 error

ERR_str: [[[{"result":"insert failed, error = 23000, message = Duplicate entry 'leerman2' for key 'username'"}],{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"","serverStatus":2,"warningStatus":0}],[[{"_b...it goes on

---end output-------

Below is the code for extracting the key:

    function getError(err) {
        const ERR_str = JSON.stringify(err);
        console.log(`Entered getError() function...proceeding to extract error...`);

        console.log(`ERR_str: ${ERR_str}`);
        console.log(`Error is string. [typeOf(ERR_str)]: ${typeof(ERR_str)}. (Attempting to extract key...`);
        const START = (ERR_str.indexOf('key')) + 'key'.length + 2; // 2 including spaces 
        console.log(`Start: ${START}`);
        const END = ERR_str.indexOf(`'"}`);
        console.log(`End: ${END}`);    
        const KEY = ERR_str.substr(START, END - START);
        console.log(`key to return to server: ${KEY}`);

        return KEY || 0;
    }

While not the most polished code, it serves as a solid starting point. I can now send it to Angular and continue working with it as intended. Any feedback on this approach is welcome.

Steps to retrieve the primary key associated with a duplicate entry error (1062) in MySQL using the Knex.js library

Answer №1

Similar questions

What are different ways to modify a bytearray within a file using angular js, whether it is an .xlsx or other

I need help figuring out how to send a POST/GET request from AJAX to a custom module controller in Odoo 10, but I'm running into issues

I am looking for a way to transfer data collected from an input form directly to my email address without the need to open a new window. As of now, I am utilizing angular

Vue: Issue with Firebase Authentication REST API triggers 400 Bad Request Error

The null evaluation consistently yields a negative outcome

What is the reason for not modifying the filtered and sorted data?

Vertically arrange the table for better visibility

Uploading multipart/form-data files in Angular with the $http module

Vue Component, switching state

Ways to stop jQuery from stripping the <script> elements

Inserting items into the document after updating the list with fresh data

Removing the JavaScript unicode character 8206 from a text string

Creating a constant in router resolve using AngularJS

Implementing a Countdown Clock in Auction Listings

"Encountering issues with logging in through AngularJS and the $http request functionality

Encountering a FileNotFound Error when attempting to access a file in a different directory using Python

Utilizing Airflow's web API to generate a JSON variable

There seems to be an issue with the bullet functionality in phaser.io

Angular log out function to automatically close pop-up windows

Tips for Angular4: ensuring ngOnDestroy completion before navigation