Mysql: how to check table is exist or not before executing any other query in connection pool

[shivarajnaidu]

I have tried the following .. but it failed..

'use strict';
const mysql    = require('mysql');
const dbConfig = require('../../../config/index').DB;
const pool     = mysql.createPool(dbConfig);

pool.query('SHOW TABLES LIKE users', (error, results) => {
    if(error) return console.log(error);
    console.log(results)
});

pool.on('connection', function (connection) {
  console.log('%d conneted', connection.threadId)
});

pool.on('release', function (connection) {
  console.log('Connection %d released', connection.threadId);
});

module.exports = pool;

SHOW TABLES LIKE "users"; this query works fine in command line mysql client but failed in node context..

[dougwilson]

Hi @shivarajnaidu I'm not 100% sure what you're asking for, because the title and the text and the code all seem to be doing different things, so I'm not sure which is the question. Would you mind rewording it?

[shivarajnaidu]

Hi @dougwilson , Thank You For Taking Time To Review My Issue..

I have executed SHOW TABLES LIKE users; in mysql command line client.. works fine.. but when i use it in node js (see the above code snippet) .. it failed with saying syntax error in SQL query ...
But SQL query works fine in mysql command line..

{ Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'users' at line 1
    at Query.Sequence._packetToError (/home/yuvaraj/mysqltst/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
    at Query.ErrorPacket (/home/yuvaraj/mysqltst/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
    at Protocol._parsePacket (/home/yuvaraj/mysqltst/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/home/yuvaraj/mysqltst/node_modules/mysql/lib/protocol/Parser.js:75:12)
    at Protocol.write (/home/yuvaraj/mysqltst/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/home/yuvaraj/mysqltst/node_modules/mysql/lib/Connection.js:103:28)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    --------------------
    at Pool.query (/home/yuvaraj/mysqltst/node_modules/mysql/lib/Pool.js:199:23)
    at Object.<anonymous> (/home/yuvaraj/mysqltst/db.js:6:6)
    at Module._compile (module.js:570:32)
    at Object.Module._extensions..js (module.js:579:10)
    at Module.load (module.js:487:32)
    at tryModuleLoad (module.js:446:12)
    at Function.Module._load (module.js:438:3)
    at Module.runMain (module.js:604:10)
    at run (bootstrap_node.js:393:7)
    at startup (bootstrap_node.js:150:9)
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  index: 0 }

[dougwilson]

Hi @shivarajnaidu gotcha. So yes, I see the issue now. Running what you posted above results in the error:

pool.query('SHOW TABLES LIKE users', (error, results) => {
    if(error) return console.log(error);
    console.log(results)
});

The issue is that you need to quote the table name, for example:

pool.query('SHOW TABLES LIKE "users"', (error, results) => {
    if(error) return console.log(error);
    console.log(results)
});

[shivarajnaidu]

Hi Bro..
Thank For Figuring out the issue..
I too just realized that i need to quote the table name after like keyword...
I just put the the same which works in command line (without quotes) for node js that is what causes the error in node context..
However thank you very much for taking time to review this (Silly issue :blush: ) ..

[dougwilson]

No problem! Without quotes doesn't even work in the command line for me:

$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.73-log Source distribution

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW TABLES LIKE users;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'users' at line 1