MySQL Login authentication server 504 error despite proper query of db

Login request was received, the username and password were correctly extracted from the request body, and a user with ID 1 was found in the database. The form still 504 fails eventually.

my index.js, db.js, users.js, and login.html all seem fine. I'm on Lightsail so unfortunately I've had to use SQL Workbench this whole time.

Not sure if there's an issue with the Lightsail to DB communication? It's been a pain to try to figure out Lightsail with the 'module' stuff like databases.

users.js :

const connection = require('./db');
const bcrypt = require('bcrypt');
const saltRounds = 10;

class User {
  constructor(id, username, password, email, createdAt, updatedAt) {
    this.id = id;
    this.username = username;
    this.password = password;
    this.email = email;
    this.createdAt = createdAt;
    this.updatedAt = updatedAt;
  }

  static create(username, password, email) {
    const now = new Date().toISOString();
    const sql = `INSERT INTO loginserver (username, password, email, created_at, updated_at) VALUES (?, ?, ?, ?, ?)`;
    bcrypt.hash(password, saltRounds, (err, hash) => {
      if (err) {
        console.error('Error hashing password:', err);
        return;
      }
      const values = [username, hash, email, now, now];
      connection.query(sql, values, (err, result) => {
        if (err) {
          console.error('Error creating user:', err);
          return;
        }
        console.log('User created with ID', result.insertId);
        const user = new User(result.insertId, username, hash, email, now, now);
        return user;
      });
    });
  }

  static getByUsername(username) {
    const sql = `SELECT * FROM loginserver WHERE username = ?`;
    connection.query(sql, [username], (err, results) => {
      if (err) {
        console.error('Error getting user by username:', err);
        return;
      }
      if (results.length === 0) {
        console.log('User not found');
        return null;
      }
      const { id, username, password, email, created_at, updated_at } = results[0];
      console.log('User found with ID', id);
      const user = new User(id, username, password, email, created_at, updated_at);
      return user;
    });
  }

  checkPassword(password) {
    return new Promise((resolve, reject) => {
      bcrypt.compare(password, this.password, (err, isMatch) => {
        if (err) {
          console.error('Error checking password:', err);
          reject(err);
        } else {
          resolve(isMatch);
        }
      });
    });
  }

  update() {
    const now = new Date().toISOString();
    const sql = `UPDATE loginserver SET username = ?, password = ?, email = ?, updated_at = ? WHERE id = ?`;
    const values = [this.username, this.password, this.email, now, this.id];
    connection.query(sql, values, (err) => {
      if (err) {
        console.error('Error updating user:', err);
        return;
      }
      console.log('User updated with ID', this.id);
      this.updatedAt = now;
      return this;
    });
  }

  delete() {
    const sql = `DELETE FROM loginserver WHERE id = ?`;
    connection.query(sql, [this.id], (err) => {
      if (err) {
        console.error('Error deleting user:', err);
        return;
      }
      console.log('User deleted with ID', this.id);
      return;
    });
  }
}

module.exports = User;

index.js :

const express = require('express');
const https = require('https');
const socketIO = require('socket.io');
const path = require('path');
const fs = require('fs');
const mysql = require('mysql');
const User = require('./server/users');
const bodyParser = require('body-parser');

const app = express();
const server = https.createServer({
  key: fs.readFileSync('/etc/letsencrypt/live/ispeedrun.tv/privkey.pem'),
  cert: fs.readFileSync('/etc/letsencrypt/live/ispeedrun.tv/fullchain.pem')
}, app);
const io = socketIO(server);

// Add this before the routes
app.use((req, res, next) => {
  console.log('Request received');
  next();
});

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

app.get('/', (req, res) => {
  res.sendFile(path.join(__dirname, 'views', 'index.html'));
});

app.get('/live', (req, res) => {
  res.sendFile(path.join(__dirname, 'views', 'live.html'));
});

const connection = mysql.createConnection({
  host: 'ls-7f5846c26112d5a110aa9ce17f20838297ce7c51.cdnunzehdfq0.us-east-2.rds.amazonaws.com',
  port: '3306',
  user: 'dbmasteruser',
  password: '',
  database: ''
});

connection.connect((err) => {
  if (err) {
    console.error('Failed to connect to MySQL:', err);
    return;
  }
  console.log('Connected to MySQL database');
});

io.on('connection', (socket) => {
  console.log('WebSocket connection established');

  socket.on('message', (msg) => {
    console.log('message: ' + msg);
    io.emit('message', msg);
  });

  socket.on('disconnect', () => {
    console.log('WebSocket connection closed');
  });
});

// add this route to handle form submission
app.post('/login', (req, res) => {
  console.log('Received login request');
  console.log('Login request received:', req.body); // Log the received request
  const { username, password } = req.body;

  User.getByUsername(username, (err, user) => {
    if (err) {
      console.error('Error getting user:', err);
      res.status(500).send('Internal server error');
      return;
    }

    if (!user) {
      res.status(401).send('Invalid username or password');
      return;
    }

    user.checkPassword(password, (err, isMatch) => {
      if (err) {
        console.error('Error checking password:', err);
        res.status(500).send('Internal server error');
        return;
      }

      if (!isMatch) {
        res.status(401).send('Invalid username or password');
        return;
      }

      res.status(200).send(); // Send a 200 status code to indicate a successful login
    });
  });
});

// Add this after the routes
app.use((req, res, next) => {
  console.log('Response sent');
  next();
});

const PORT = process.env.PORT || 6611;
server.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});

login.html :

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>iSpeedrun.TV - Login</title>
  <link rel="stylesheet" href="styles.css">
  <style>
    /* Keep the same styles as index.html */
    .main-container {
      display: flex;
      flex-direction: row;
    }

    .video-container {
      width: 1280px;
      height: 720px;
      margin-right: 20px;
    }

    .video-container iframe {
      width: 100%;
      height: 100%;
    }

    .sidebar {
      width: 300px;
      height: 720px;
      display: flex;
      flex-direction: column;
      justify-content: space-between;
    }

    .sidebar-item {
      display: flex;
      align-items: center;
      padding: 10px;
      background-color: #222;
      color: #fff;
      font-size: 14px;
    }

    .sidebar-item img {
      width: 60px;
      height: 60px;
      margin-right: 10px;
    }

    header {
      display: flex;
      justify-content: space-between;
      align-items: center;
      background-color: #222;
      color: #fff;
      padding: 10px;
    }

    nav ul {
      display: flex;
      list-style: none;
      padding: 0;
      margin: 0;
    }

    nav li {
      margin-right: 20px;
    }

    nav a {
      color: #fff;
      text-decoration: none;
      font-weight: bold;
      font-size: 16px;
      text-transform: uppercase;
    }

    nav a:hover {
      color: #ff0000;
    }

    .login-container {
      background-color: #fff;
      padding: 40px;
      border-radius: 10px;
      width: 70%;
      margin: 20px auto;
      box-shadow: 0 0 20px rgba(0, 0, 0, 0.5);
    }

    .login-container label {
      font-size: 20px;
      margin-bottom: 20px;
    }

    .login-container input[type="text"],
    .login-container input[type="password"] {
      width: 100%;
      height: 40px;
      margin-bottom: 30px;
      padding: 10px;
      font-size: 16px;
      border-radius: 5px;
      border: none;
      box-shadow: 1px 1px 5px rgba(0, 0, 0, 0.3);
    }

    .login-container button[type="submit"] {
      display: block;
      width: 100%;
      height: 50px;
      background-color: #e74c3c;
      color: #fff;
      border: none;
      border-radius: 5px;
      font-size: 18px;
      cursor: pointer;
      transition: background-color 0.2s;
    }

    .login-container button[type="submit"]:hover {
      background-color: #c0392b;
    }

    #message {
      font-size: 18px;
      color: red;
      margin-bottom: 15px;
    }
  </style>
</head>
<body>
<header>
  <h1>iSpeedrun.TV - Login</h1>
  <nav>
    <ul>
      <li><a href="index.html">Home</a></li>
      <li><a href="livestream.html">Live Streams</a></li>
      <li><a href="about.html">About Us</a></li>
      <li><a href="contact.html">Contact</a></li>
      <li><a href="login.html">Login</a></li>
</ul>
  </nav>
</header>
<main class="main-container">
  <div class="sidebar">
    <div class="sidebar-item">
      <img src="https://via.placeholder.com/60x60.png?text=User+1" alt="User 1">
      <p>User 1</p>
    </div>
    <div class="sidebar-item">
      <img src="https://via.placeholder.com/60x60.png?text=User+2" alt="User 2">
      <p>User 2</p>
    </div>
    <div class="sidebar-item">
      <img src="https://via.placeholder.com/60x60.png?text=User+3" alt="User 3">
      <p>User 3</p>
    </div>
    <div class="sidebar-item">
      <img src="https://via.placeholder.com/60x60.png?text=User+4" alt="User 4">
      <p>User 4</p>
    </div>
  </div>
  <div class="video-container">
    <form class="login-container" action="/login" method="post" id="login-form">
      <label for="username">Username:</label>
      <input type="text" id="username" name="username">
      <label for="password">Password:</label>
      <input type="password" id="password" name="password">
      <div id="message"></div>
      <button type="submit">Login</button>
    </form>
  </div>
</main>
  <script>
  const form = document.getElementById('login-form');
  const message = document.getElementById('message');

  form.addEventListener('submit', async function(event) {
    console.log('Form submitted');
    event.preventDefault(); // Prevent the form from submitting normally
    const username = document.getElementById('username').value;
    const password = document.getElementById('password').value;
    try {
      console.log('Sending request to server');
      const response = await fetch('/login', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
        },
        body: JSON.stringify({ username, password }),
      });
      console.log('Server responded with status:', response.status);

      if (response.status === 200) {
        localStorage.setItem('loggedIn', 'true');
        window.location.href = 'index.html';
      } else {
        const error = await response.json();
        message.textContent = error.message;
      }
    } catch (error) {
      console.error('Error:', error);
      message.textContent = 'An error occurred. Please try again.';
    }
  });
   </script>
 </body>
</html>