Nodejs, Express mysql always returning ER_BAD_DB_ERROR

Question

I'm developing a simple web app with Node.js and Express. I have to query a database using mysql module. Every time I try to query the database it shows up this error:

  code: 'ER_BAD_DB_ERROR',
  errno: 1049,
  sqlMessage: "Unknown database 'moneydb'",
  sqlState: '42000',
  fatal: true

I tried to create e a new database but I have always the same error. Wamp is running. credentials are right beacuse if I navigate to localhost/phpmyadmin i log in with root as username and no password.

I post here the relevant code:

app.js

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'pug');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', indexRouter);
app.use('/users', usersRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

module.exports = app;

index.js

var express = require('express');
var router = express.Router();
var mysql = require('mysql');

// connection setup
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'moneydb'
});


// connect to database
connection.connect((err) => {
    if (err) {
        console.log('Not connected to database');
        throw err;
    } else {
        console.log('Connected to database');
    }
});

// make connection global
global.db = connection;

/*
*  Every page we declare here will be under the index path
*  localhost:3000/ will render home page
*  localhost:3000/disheslist will be under the home page path and will render the disheslist
* */

/* GET home page. */
router.get('/', function (req, res, next) {
    res.render('index', {title: 'Express'});
});

/*GET dish list. */
/*render contains views(pug files)*/
router.get('/dishes', function (req, res, next) {
    let query = "SELECT * FROM `dish` ORDER BY id ASC";

    // execute query
    db.query(query, (err, result) => {
        if (err) {
            // we should redirect to an error page maybe
            res.redirect('/');
        }

        if (result) {
            res.render('dishes', {tableData: result});
        } else {
            res.send("Error");
        }
    });
});

module.exports = router;

dishes.pug

doctype html
html
    head
        title = title
        link(rel='stylesheet', href='/stylesheets/style.css')
    body
    table
        thead
            tr
            th id
            th name
            tbody

What's wrong? Thank you!

your server says there is no database with that name so check, eventually a typo — nbk
– nbk, Commented Feb 29, 2020 at 13:20
No typo. I created another database, copied and pasted the name from the database into the code, and I have the same error, I think it' s something else, but I don' t know what. — SpaghettiFunk
– SpaghettiFunk, Commented Feb 29, 2020 at 14:22
enable the logs of mysql and see what reaches the server also check error log and log if there are events in the system log — nbk
– nbk, Commented Feb 29, 2020 at 14:30
You mean mysql on the server where I created the database or the mysql Nodejs module? — SpaghettiFunk
– SpaghettiFunk, Commented Feb 29, 2020 at 14:42

SpaghettiFunk · Accepted Answer · 2020-02-29 15:12:22Z

Problem solved.

I checked which port was used from MySql on wamp and I found the 3308 port. I added the port to the configuration before connecting in the index.js and I got it. Code updated here:

index.js

var express = require('express');
var router = express.Router();
var mysql = require('mysql');

// connection setup
const connection = mysql.createConnection({
    host: 'localhost',
    port: 3308,
    user: 'root',
    password: '',
    database: 'dbname'
});


// connect to database
connection.connect((err) => {
    if (err) {
        console.log('Not connected to database');
        throw err;
    } else {
        console.log('Connected to database');
    }
});

// make connection global
global.db = connection;

/*
*  Every page we declare here will be under the index path
*  localhost:3000/ will render home page
*  localhost:3000/disheslist will be under the home page path and will render the disheslist
* */

/* GET home page. */
router.get('/', function (req, res, next) {
    res.render('index', {title: 'Express'});
});

/*GET dish list. */
/*render contains views(pug files)*/
router.get('/dishes', function (req, res, next) {
    let query = "SELECT * FROM `dish` ORDER BY id ASC";

    // execute query
    db.query(query, (err, result) => {
        if (err) {
            // we should redirect to an error page maybe
            res.redirect('/');
        }

        if (result) {
            res.render('dishes', {tableData: result});
        } else {
            res.send("Error");
        }
    });
});

module.exports = router;

Thank you all!

utkarsh upadhyay · Accepted Answer · 2020-02-29 13:33:46Z

0

Looks like user do not have permission on database server. Looking at the error message seems like request is able to reach server however due to insufficient privileges an error is returned by the server.

answered Feb 29, 2020 at 13:33

utkarsh upadhyay

1

1 Comment

SpaghettiFunk Over a year ago

What kind of privileges? Where I should check?

PM 77-1 · Accepted Answer · 2022-01-06 22:40:09Z

0

I commented/took the database: '<DB NAME>' out of the object.

const connection = mysql.createConnection({
  user : 'root',
  host : '127.0.0.1',
  //database : '<db name>',
  port: '3306',
  password : '<your password>'
});

edited Jan 6, 2022 at 22:40

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

answered Jan 6, 2022 at 21:20

Chris Waters

14 bronze badges

1 Comment

Dylan Kerler Over a year ago

Please add an explanation for your answer

Syscall · Accepted Answer · 2022-02-27 11:43:51Z

0

problem solved - changed port number. mysql db port is 3360 and in index.js port should be 3360.

await createConnection({
  host: "localhost",
    type: "mysql",
    port: 3360,
    database: "graphqlschema1",
    username: "root",
    password: "sksuman",
    logging: true,
    synchronize: false,
    entities: [],
  });

edited Feb 27, 2022 at 11:43

Syscall

19.8k10 gold badges44 silver badges60 bronze badges

answered Feb 27, 2022 at 10:45

Sunil Kumar Suman

594 bronze badges

Comments

Kunal Kalwar · Accepted Answer · 2024-02-27 10:31:42Z

0

In mac I navigated to /Applications/MAMP/bin/phpMyAdmin/config.inc.php

And found that my username was "root" and password was also "root"

$cfg['Servers'][$i]['user']          = 'root';      
$cfg['Servers'][$i]['password']      = 'root';

answered Feb 27, 2024 at 10:31

Kunal Kalwar

1,00517 silver badges29 bronze badges

Comments

RBT · Accepted Answer · 2022-09-22 06:19:32Z

-1

Go to terminal in ubuntu in MySQL. Create a database:

CREATE DATABASE yourdatabasename

It will resolve your issue.

edited Sep 22, 2022 at 6:19

RBT

26.3k24 gold badges178 silver badges267 bronze badges

answered Sep 15, 2022 at 7:30

user20001325

1

Collectives™ on Stack Overflow

Nodejs, Express mysql always returning ER_BAD_DB_ERROR

6 Answers 6

Comments

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related