MySQL Node.js Query Ends, no Callback

Question

I'm using mysql for node.js.

This query checks if a column has all NULL values, by checking if at least one row is not NULL.

SELECT 1 FROM `table` WHERE `column` IS NOT NULL LIMIT 1

If the column has all NULL values, this query ends in the SQL processlist after a few seconds (~120), but I never get a callback from the js code:

import {createPool as mysqlCreatePool, MysqlError} from 'mysql';

const db = mysqlCreatePool({...})

const query = db.query(sql, undefined, function (err: MysqlError | null, res?: any) {
  console.log(err, res);
});

query.on('end', () => console.log("end"));

(Nothing is ever printed in the console)

dusthaines · Accepted Answer · 2020-03-24 18:52:44Z

1

Might not be the same approach but will get you the answer I think you're after and always return a result.

Query for a count rather than a fixed value and use IS NULL rather than IS NOT NULL:

SELECT count(*) AS null_count FROM `table` WHERE `column` IS NULL;

If your response is 0 every record has a value for that column. If your response is greater than 0 you have that many records with a NULL value in that column.

answered Mar 24, 2020 at 18:52

dusthaines

1,3501 gold badge11 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amit Over a year ago

I understand how this will always get a result, but, this is much more expensive time-wise, as the other method is negation based (NOT NULL LIMIT 1) and works really fast. Any idea specifically why my query doesn't return anything on nodejs musql?

dusthaines Over a year ago

You could try a variation of the CASE WHEN approach. I'm not familiar with the performance impact compared to my prior example and your original. Consider this approach: SELECT (column IS NOT NULL) AS null_check FROM table LIMIT 1. Testing each of the 3 variations on a remote DB table with ~120k records, all have a similar response time of appx. 16ms to 18ms. More details here on the alternate approach : (stackoverflow.com/a/9387864/1720873)

Collectives™ on Stack Overflow

MySQL Node.js Query Ends, no Callback

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related