Resolving API and MySQL Connection Issues in Node.js: A Case Study on “SampleAPI”

This case study outlines the troubleshooting and solutions applied to resolve issues encountered while setting up and running a Node.js-based system, “SampleAPI,” with MySQL. The client faced challenges such as API inaccessibility, port conflicts, and MySQL connection errors. Below is a detailed breakdown of the identified issues, implemented fixes, and a step-by-step guide to resolve similar problems.

Client’s Issue Overview

The client reported the following issues:

1. API Not Responding Properly

• Logs: The Node.js API appeared to be running in the logs but was inaccessible externally.
• Port Change: The client changed the default port from 3002 to 3003 but still couldn’t access the API.

2. Firewall Configuration Challenges

• Port Access: Attempts to enable port access for the Node.js application were met with firewall password requests, causing issues.

3. MySQL “Not Running” Error

• phpMyAdmin Access: The client could access the database via phpMyAdmin, but the Node.js application displayed errors indicating that MySQL wasn’t running.

Immediate assistance was requested to resolve the issues and ensure proper system configuration.

Solution Breakdown

1. Diagnosing the API Issue

The first task was to determine why the API wasn’t responding despite the logs indicating it was running.

Port Conflict Identification

Initial Diagnosis: A conflict or another process might be using port 3002, preventing the API from functioning.

Command Used:

sudo lsof -i :3002

This command confirmed that another[previously running] Node.js instance was running on port 3002.

Fixing the Conflict

Solution: Terminate the conflicting process using the command:

kill -9 <PID>

This freed the port, allowing the application to bind successfully to port 3002.

2. Updating the API to Listen on Port 3003

Since the client intended to use port 3003, we verified the server configuration to ensure it was set correctly.

Server.js Configuration

The server.js file was updated to listen on port 3003:

const port = process.env.PORT || 3003;

Restarting the Application

To apply the changes, the application was restarted using PM2:

pm2 restart nodejs-app-dev --update-env

3. Checking Node.js Application Logs

When troubleshooting a Node.js application, reviewing the application logs is crucial. Logs provide detailed information on errors, warnings, or any unusual behavior that could point to underlying issues. Whether you’re managing your Node.js processes with a tool like PM2 or running the application directly, knowing how to access and interpret these logs is key to efficient debugging.

Checking Logs with PM2

If you are using PM2 to manage your Node.js processes, checking the logs is straightforward. PM2 not only simplifies process management but also consolidates logging, making it easy to review real-time activity or narrow down specific issues.

To view real-time logs

pm2 logs <app-name>

This command will stream the logs for the specified application, showing real-time activity, including status updates and error messages.

To view a specific number of log lines

If you want to check recent logs without streaming, you can limit the output by specifying the number of lines:

pm2 logs --lines 100 <app-name>

This command displays the last 100 lines of logs for your application, allowing you to focus on recent events.

Checking Logs Without PM2

If you’re not using PM2 and are running your Node.js application manually, you can still capture and review logs efficiently.

Redirect output to a log file

When running a Node.js application directly, you can capture both standard output (stdout) and error output (stderr) in a log file:

node <app-name>.js > app.log 2>&1

This command directs all logs and errors to app.log, which you can review later.

Monitor logs in real-time with Tail

If you want to monitor your logs as they are generated, you can use the tail command:

tail -f app.log

This will show new log entries as they are written, which is useful for real-time troubleshooting during application execution.

4. Configuring Firewall Settings

The client faced difficulties opening port 3003 due to issues with the SSH command. To resolve this, we added the port to the client’s application instance firewall. Additionally, we provided the client with a detailed article for reference, enabling them to independently open the port via the AWC Dashboard if needed in the future: How to Open a New Port in Firewall Using AWC Dashboard?

5. Resolving MySQL Connectivity Issues

Despite MySQL being accessible via phpMyAdmin, the Node.js application could not establish a connection.

Step 1: Verifying MySQL Status

We confirmed that MySQL was running by executing:

sudo systemctl status mysqld

Step 2: Testing MySQL Connection

We used the credentials from the .env file to test MySQL access from the command line

mysql -h <mysql-server-ip> -u root -p

The test confirmed that the credentials were correct and that MySQL was accessible.

Step 3: Updating Environment Variables

It was discovered that the Node.js application had incorrect credentials in the .env file. We updated the DB_HOST to reflect the correct MySQL server IP

DB_HOST=<your-mysql-server-private-ip>

Step 4: Restarting the Application

The Node.js application was restarted to apply the updated credentials:

pm2 restart <app-name>

6. Adding a Route Handler for the Root URL

During testing, the API returned a “Cannot GET /” error at the root URL, indicating no route handler was defined for GET requests to the root.

Solution: Adding a Root Route

We implemented a simple route handler in server.js:

app.get('/', (req, res) => { res.send('Welcome to the API!'); });

Restarting and Verifying the Change

The application was restarted, and upon accessing the root URL, the message “Welcome to the API!” was returned, confirming the fix.

7. Verifying the Fixes

Once the changes were implemented, we conducted several tests to confirm the system was functioning properly:

API Testing

We accessed the API via http://localhost:3003 and verified that it was responding correctly.

Database Connection Testing

API endpoints requiring MySQL access were tested, and data was returned as expected, confirming that the connection issues were resolved.

Summary of Actions Taken

1. Port Conflict Resolution: Identified and terminated the conflicting Node.js process.
2. Port Update: Configured the application to listen on port 3003.
3. Firewall Configuration: Enabled traffic on port 3003 via the AWC Dashboard.
4. MySQL Connection Fix: Updated the .env file with correct MySQL credentials and restarted the application.
5. Route Handler Addition: Added a root route handler to return a welcome message.
6. API Verification: Tested the API to confirm full functionality.

Final Outcome

The API and MySQL connection issues were successfully resolved. The “SampleAPI” system is now fully operational and accessible at:

This guide provides a comprehensive reference for developers troubleshooting similar issues in Node.js applications involving port conflicts, firewall configurations, and MySQL connectivity.