MySQL Debugging using Python and NodeJS

Welcome to the first installment of our two-part series on debugging and tracing with MySQL. MySQL has become a keystone of the tech world, supporting everything from personal blogs to sophisticated e-commerce sites. Its ability to adapt and maintain robustness has made MySQL indispensable in the digital age. Due to its scalability and efficient handling of complex data structures, MySQL is a core component for many application use cases.

However, despite MySQL’s evolution into an adaptive database, debugging can present significant challenges, particularly when integrating with different programming environments such as Python and Node.js. Pinpointing and resolving issues within applications can be time-consuming and often frustrating, involving thorough log analysis and understanding the unique behaviors of each programming language when interacting with MySQL.

In this post, we’ll explore these challenges and provide strategies for overcoming them. We’ll also create a demonstration environment using Docker to containerize Python Flask and Node.js applications with a MySQL database. This setup helps in replicating and debugging common issues encountered in the wild without the sake of

Setting Up the Demonstration Environment

First, ensure Docker is installed on your machine or development environment. You’ll also need a Lumigo account, which you can set up super quick. Later in the demo build, we will need the token from your Lumigo account to enable OpenTelemetry integration.

Create a project directory and navigate into it:

mkdir flask_node_mysql_demo && cd flask_node_mysql_demo

Within this directory, create separate subdirectories for the Flask and Node.js applications:

mkdir flask_app node_app

Then, create a docker-compose.yml file at the project’s root, specifying the configuration for the Flask and Node.js applications and the MySQL database service.

version: ‘3’
services:
  flask_app:
    container_name: flask_app_container
    build: ./flask_app
    ports:
      – “3050:3050”
    depends_on:
      – db
    environment:
      – LUMIGO_TRACER_TOKEN=${LUMIGO_TRACER_TOKEN}
 
  node_app:
    container_name: node_app_container
    build: ./node_app
    ports:
      – “3000:3000”
    depends_on:
      – db
    environment:
      – LUMIGO_TRACER_TOKEN=${LUMIGO_TRACER_TOKEN}
 
  db:
    container_name: mysql_db_container
    image: mysql:8.3
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: demo
    ports:
      – “3306:3306”

It’s important to note that setting the database password in the docker-compose file is only suitable for demonstration or prototyping purposes and should not be used in production environments.

In the flask_app directory, create a Dockerfile to define the Python Flask application’s environment, including installing necessary dependencies and exposing the appropriate port.

FROM python:3.12-slim
WORKDIR /app
COPY . /app
RUN pip install Flask mysql-connector-python lumigo_opentelemetry
EXPOSE 3050
ENV OTEL_SERVICE_NAME=”python-app”
ENV AUTOWRAPT_BOOTSTRAP=lumigo_opentelemetry
CMD [“python”, “app.py”]

Next, also within the flask_app directory, create an app.py file to define the Flask application’s routes and database interactions.

from flask import Flask, request, jsonify
import pymysql.cursors
 
app = Flask(__name__)
 
def create_connection():
    return pymysql.connect(
        host=’db’,
        user=’root’,
        password=’password’,
        database=’demo’,
        cursorclass=pymysql.cursors.DictCursor
    )
 
@app.route(‘/’)
def hello_world():
    return ‘Hello World’
 
@app.route(‘/records’, methods=[‘GET’, ‘POST’])
def handle_records():
    connection = create_connection()
    try:
        with connection.cursor() as cursor:
            if request.method == ‘POST’:
                sql = “INSERT INTO records (name) VALUES (%s)”
                cursor.execute(sql, (request.json[‘name’],))
                connection.commit()
                return jsonify(message=”Record added successfully”)
            else:
                sql = “SELECT * FROM records”
                cursor.execute(sql)
                records = cursor.fetchall()
                return jsonify(records)
    except Exception as e:
        return jsonify(message=str(e)), 500
    finally:
        connection.close()
 
if __name__ == ‘__main__’:
    app.run(host=’0.0.0.0′, port=3050)

Repeat a similar process for the Node.js application in the node_app directory, starting with a Dockerfile to set up the environment and then an app.js file to define the application logic and database connectivity.

Dockerfile:

FROM node:20
WORKDIR /app
COPY package*.json ./
RUN npm install
COPY . .
EXPOSE 3000
ENV OTEL_SERVICE_NAME=”express-app”
ENV NODE_OPTIONS=”-r @lumigo/opentelemetry”
CMD [“node”, “app.js”]

App.js

const express = require(‘express’);
const mysql = require(‘mysql2’);
const app = express();
app.use(express.json());
 
function connectWithRetry(attemptsLeft = 5) {
  const connection = mysql.createConnection({
    host: ‘db’,
    user: ‘root’,
    password: ‘password’,
    database: ‘demo’
  });
 
  connection.connect(error => {
    if (error) {
      console.error(‘Failed to connect to db, retrying…’, error);
      if (attemptsLeft > 0) {
        setTimeout(() => connectWithRetry(attemptsLeft – 1), 5000); // Wait 5 seconds before retrying
      } else {
        throw error;
      }
    } else {
      console.log(‘Successfully connected to the database.’);
      global.dbConnection = connection; // Make the connection globally available
    }
  });
}
 
connectWithRetry();
 
app.get(‘/’, (req, res) => {
  res.send(‘Hello World’);
});
 
// Use the global.dbConnection in your routes
app.get(‘/records’, (req, res) => {
  global.dbConnection.query(‘SELECT * FROM records’, (error, results) => {
    if (error) throw error;
    res.json(results);
  });
});
 
app.post(‘/records’, (req, res) => {
  const { name } = req.body;
  global.dbConnection.query(‘INSERT INTO records SET ?’, { name }, (error, results) => {
    if (error) throw error;
    res.json({ message: ‘Record added successfully’ });
  });
});
 
app.listen(3000, () => {
  console.log(‘Node.js app is listening on port 3000’);
});

package.json

{
  “name”: “node_app”,
  “version”: “1.0.0”,
  “description”: “”,
  “main”: “app.js”,
  “scripts”: {
    “start”: “node app.js”
  },
  “dependencies”: {
    “@lumigo/opentelemetry”: “^1.37.0”,
    “express”: “^4.18.3”,
    “mysq2l”: “^3.9.2”
  }
}

With the Dockerfiles and application code in place, return to the root directory and start the containers using Docker Compose, ensuring to replace your_token_here with your actual Lumigo token:

LUMIGO_TRACER_TOKEN=your_token_here docker-compose up -d

This command will build and start the Flask and Node.js application containers, making them accessible on their respective ports and connected to the MySQL database. Allow a few minutes for the setup to initialize. The Node.js application may initially report connection errors until the MySQL database is fully operational.

Once everything is running, you can access the demo applications in a web browser at http://localhost:3050/ for the Flask app and http://localhost:3000/ for the Node.js app. If set up correctly, both should display a basic greeting message.

One advantage of using Lumigo for instrumentation is that it doesn’t require modifications to your existing codebase, simplifies the deployment of OpenTelemetry, and allows you to monitor your applications without disruption.

Check out part 2 of this series, where we’ll explore debugging challenges and provide further ways to improve your troubleshooting processes when working with MySQL within app deployments.