Custom database error - "mysql is not a function"?

[maxtor]

When setting up a custom database connection, using the “mysql” template (completely unedited, but even edited results in same error) and click “Try” I receive the output in the try window “mysql is not a function”.

I have no idea what’s happening and can’t find any documentation on this issue, please help? Again, I’m using a 100% default mysql template and something is clearly wrong?

[markd]

Looks like the code in the template is wrong and / or outdated. Line 5 should be calling mysql.createConnection().

mysql

[konrad.sopala]

Hey @maxtor!

Can you send me the link to the template you’re using so we can make adjustments accordingly?

Thanks a lot!

[maxtor]

Honestly, it’s a little frustrating to see such basic things get missed, wasted a nice 45min of my time trying to see what I might be doing wrong here.

Connections → Username-Password-Authentication → Custom database → Templates dropdown → mySQL

Still waiting for the correct script, the suggestion above didn’t work

[markd]

The screencap he provided is the default mysql login script template from the management console. I get the same one in a new tenant. The template in the docs needs some work too (doesn’t import mysql or bcrypt).

[markd]

Hi @maxtor,

Changing the const connection = line should get you started. I update the template in my own tenant and got a connection error instead (expected, no actual mysql instance to connect to). If you replaced mysql with mysql.createConnection on that line, are you still getting the same error?

  const connection = mysql.createConnection({
    host: 'localhost',
    user: 'me',
    password: 'secret',
    database: 'mydb'
  });

[maxtor]

Thanks, this appears to do the trick after all I’m now getting 401 unauthorized, even though the login credentials are correct for this email/pw combo, but I suppose I can troubleshoot that on my own.

Edit: my app’s passwords were hashed with PHP’s bcrypt…which apparently is not the same as node’s bcrypt, sigh. Fix here: Can't match Bcrypt hash generated in PHP using Node Bcrypt function when configuring custom DB - #7 by antoine.thierry

[markd]

I tried mocking this up with a GCP Cloud SQL MySQL instance but I was getting 401s as well. May be the same problem as I had bcrypted my passwords with a web site bcrypt tool (probably using php).

"2019-02-07T15:40:13.191883Z 252 [Note] Aborted connection 252 to db: \
  'users' user: 'auth0' host: '35.166.202.113' (Got an error reading \
  communication packets)"  

I also got pretty frequent socket timeouts when testing the connection, but I think those were related to saving the script and waiting for the script to be deployed to webtask.io.

0:54:11 AM:
 new webtask request 1549554851342.756635
10:54:21 AM:
 { Error: connect ETIMEDOUT
at Connection._handleConnectTimeout (/data/_verquire/mysql/2.7.0/node_modules/mysql/lib/Connection.js:362:13)
.
.
.
10:54:21 AM:
 finished webtask request 1549554851342.756635 with HTTP 200 in 10134ms

[markd]

The script below is working for me (some extra console.log() lines that can be removed).

function login(email, password, callback) {
  const mysql = require('mysql');
  const bcrypt = require('bcrypt');

  const connection = mysql.createConnection({
    host: configuration.ip,
    user: configuration.user,
    password: configuration.password,
    database: configuration.database
  });
  
  //console.log(connection);
  
  connection.connect();

  const query = 'SELECT id, nickname, email, password FROM users WHERE email = ?';
  
  connection.query(query, [ email ], function(err, results) {
    if (err) console.log(err);
    if (err) return callback(err);
    if (results) console.log(results);
    if (results.length === 0) return callback(new WrongUsernameOrPasswordError(email));
    const user = results[0];

    bcrypt.compare(password, user.password, function(err, isValid) {
      if (err || !isValid) return callback(err || new WrongUsernameOrPasswordError(email));
      callback(null, {
        //user_id: user.id.toString(),
        user_id: user.email,
        nickname: user.nickname,
        email: user.email
      });
    });
  });
}