Mysql: Issue with stored producers

[balasasidhar]

id value remains same if I'm using stored procedures.

Sample o/p:

[
  {
    "id": 24994321358913544,
    "mobile_number": 1234567890,
    "name": "Test",
    "email": "[email protected]"
  },
  {
    "id": 24994321358913544,
    "mobile_number": 1234567891,
    "name": "Test1",
    "email": "[email protected]"
  }
]

[dougwilson]

Hi @sasidhar678 we'd be happy to help, but there doesn't seem to be enough information to do anything. If you're not sure what else to provide, you can start with the following, of course:

1. The version of Node.js you are using
2. The version of this module you are using
3. Complete Node.js code you are running that produces the output you showed above
4. Complete DDL for the tables needed to run your query
5. The stored procedure code
6. Complete instructions for how to run your code to see the issue

Thanks!

[balasasidhar]

1. Node v7.4.0
2. MySQL module v2.13.0
3. Node JS Code

const mysql = require('mysql');
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'sample',
    multipleStatements: true
});

pool.getConnection(function (err, connection) {
           if (err) {
                console.log(err);
                res.status(500).json(JSON.stringify(err));
            } else {

                 connection.query("CALL SP_InsertUserInfo(?, ?,  ?, ?, @uid); SELECT @uid AS uid",
                        [mobile_number, password, name, email],
                        function (err, result, fields) {
                            connection.release();
                            if (err) {
                                res.status(500).json(JSON.stringify(err));
                            } else {
                                let resData = {
                                    "message": "success",
                                    "uid": result[2][0].uid
                                };
                                res.status(200).json(resData);
                            }
                        });
            }

1. DDL

CREATE TABLE `accounts` (
  `id` bigint(20) NOT NULL,
  `mobile_number` bigint(20) NOT NULL,
  `password` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_mobile` (`mobile_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_details` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `account_id` bigint(20) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_id` (`account_id`),

  CONSTRAINT `user_details_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1. Stored Procedure

DELIMITER ;;
CREATE PROCEDURE `SP_InsertUserInfo`(IN p_mobile_number BIGINT(20), IN p_password VARCHAR(100), IN p_name VARCHAR(100), IN p_email VARCHAR(100), OUT p_uid BIGINT(20))
    SQL SECURITY INVOKER
BEGIN

DECLARE uid BIGINT UNSIGNED;
  SET uid = UUID_SHORT();
insert Into accounts
(
  id, 
  mobile_number, 
  password
) 
VALUES 
(
  uid, 
  p_mobile_number, 
  p_password
);

insert into user_details 
(
  account_id,
  name,
  email
) 
VALUES 
( 
  uid,
  p_name,
  p_email
);

SET p_uid =  uid;

END ;;
DELIMITER ;

1. Try to insert more than 1 record and then retrieve all as well as single record using WHERE clause. All record ids holds the same value.

[bbito]

CREATE TABLE accounts (
id bigint(20) NOT NULL,

Just a guess, but is the behavior corrected if you define your id fields as UNSIGNED?

CREATE TABLE `accounts` (
  `id` bigint(20) UNSIGNED NOT NULL, 

[balasasidhar]

That's not an issue. ids are inserting properly in database. But in the Node js the ids are not updating with latest/actual values.

DB Values

NODE JS Values for SELECT statement

[
  {
    "id": 24994985602449410,
    "mobile_number": 1234567890,
    "name": "Test",
    "email": "[email protected]",
    "role": 24947883602083870,
    "permissions": 24947883602083876
  },
  {
    "id": 24994985602449410,
    "mobile_number": 1234567891,
    "name": "Test",
    "email": "[email protected]",
    "role": 24947883602083870,
    "permissions": 24947883602083876
  },
  {
    "id": 24994985602449412,
    "mobile_number": 1234567892,
    "name": "Test",
    "email": "[email protected]",
    "role": 24947883602083870,
    "permissions": 24947883602083876
  },
  {
    "id": 24994985602449412,
    "mobile_number": 1234567893,
    "name": "Test",
    "email": "[email protected]",
    "role": 24947883602083870,
    "permissions": 24947883602083876
  },
  {
    "id": 24994985602449416,
    "mobile_number": 1234567894,
    "name": "Test",
    "email": "[email protected]",
    "role": 24947883602083870,
    "permissions": 24947883602083876
  },
  {
    "id": 24994985602449416,
    "mobile_number": 1234567895,
    "name": "Test",
    "email": "[email protected]",
    "role": 24947883602083870,
    "permissions": 24947883602083876
  }
]

[dougwilson]

Ah, if those are big numbers, this is a limitation of JavaScript / Node.js itself. Have you tried to enable any of the big number options this module provides? The easiest one is to have this module give your big numbers as strings, avoiding the JavaScript limitation.

[balasasidhar]

@dougwilson thanks it's working after enabling the supportBigNumbers and bigNumberStrings.

Thanks a lot !!

[dougwilson]

Hi @sasidhar678 glad that was the answer :) I didn't realize that's what was happening from the initial post