balderdashy / balderdashy/sails
Using sails.sendNativeQuery to call stored procedure doesn't return when the procedure has a duplicate column
- 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
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