balderdashy / balderdashy/sails

Using sails.sendNativeQuery to call stored procedure doesn't return when the procedure has a duplicate column

Open
#7,005 2 comments 0 reactions 0 assignees View on GitHub
mysql orm question try this out please
Dominant language
JavaScript
Stars
22.8k
Forks
1.9k
PR merge metrics
No merged PRs in 30d

Description

**Node version**: 12.14.1
**Sails version** _(sails)_: 1.2.4
**ORM hook version** _(sails-hook-orm)_: 2.1.1
**Sockets hook version** _(sails-hook-sockets)_: 2.0.0
**Organics hook version** _(sails-hook-organics)_: N/A
**Grunt hook version** _(sails-hook-grunt)_: 3.1.0
**Uploads hook version** _(sails-hook-uploads)_: N/A
**DB adapter & version** _(e.g. sails-mysql@5.55.5)_: sails-mysql@1.0.1
**Skipper adapter & version** _(e.g. skipper-s3@5.55.5)_: N/A


Hi,

I have a MySQL stored procedure I'm calling via sails.sendNativeQuery like so:

```
try {
let result = await sails.sendNativeQuery('CALL create_branch($1, $2, $3, $4, $5, $6);', [branch, project, step, 0, parent, user]);
if (typeof result.rows !== 'undefined') {
if (typeof result.rows[0] !== 'undefined') {
for (let row in result.rows[0]) {
if (typeof result.rows[0][row]['@full_error'] !== 'undefined') {
req.status(400).send('could not create branch: ' + result.rows[0][row]['@full_error']);
return;
}
}
}
}
} catch (err) {
sails.log.error('could not create branch, params ' + req.params + err);
res.status(400).send('could not create branch: ' + err);
return;
}
```

The stored procedure does some preflight checks and executed SIGNAL SQLSTATE '45000' when there is an error, like so:

```
IF (SELECT id FROM table WHERE table.name = _branch_name AND project = _project AND step = _step LIMIT 1) IS NOT NULL THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'This branch already exists';
END IF;
```

This appears to work fine.

However, we recently had an issue where there was a duplicate column name in one of the queries in the procedure, it did not return any errors and the request hung. When I ran the procedure directly against the database, it told me there was a duplicate column and exited. Is there a way to catch those types of issues?

Contributor guide

Open the contributing guide

Research direction

Reproduce the hang through sails.sendNativeQuery with the reported sails-mysql@1.0.1 setup and a stored procedure containing a duplicate column. Start by tracing the native-query error path in the MySQL adapter; done means the duplicate-column failure is surfaced to the caller instead of leaving the request pending, with a regression test if the existing test suite covers this path.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, mysql, node.js
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.