Error inserting basic data in cloud sql instance

[domalca]

Hi every one,   I started learning GCP one and half months ago and I'm blocking in a basic task like insert data in a cloud sql instance. I'm trying it throgh cloud functions like this:                                                                      

const mysql = require('mysql2/promise');

let pool;

exports.completePayment = async (req, res) => {

    res.set('Access-Control-Allow-Origin', '*');

    res.set('Access-Control-Allow-Methods', 'POST');

    res.set('Access-Control-Allow-Headers', 'Content-Type');

    if (req.method === 'OPTIONS') {

        res.status(204).send('');

        return;

    }

    const { menu, price, cardNumber } = req.body;

    if (!menu || !price || !cardNumber) {

        res.status(400).send('Missing data');

        return;

    }

    const priceDecimal = parseFloat(price);

    if (isNaN(priceDecimal)) {

        res.status(400).send('Invalid price');

        return;

    }

    if (!pool) {

        try {

            pool = await mysql.createPool({

                user: process.env.DB_USER,

                password: process.env.DB_PASS,

                database: process.env.DB_NAME,

                host: '34.175.115.163',

                port: 3306,

                connectTimeout: 10000

            });

            console.log('Database pool created successfully');

        } catch (error) {

            console.error('Error creating database pool:', error);

            res.status(500).send('Error connecting to the database');

            return;

        }

    }

    //cnx test

    try {

        const connection = await pool.getConnection();

        console.log('Database connection successful');

        connection.release();

    } catch (error) {

        console.error('Error connecting to the database:', error.message);

        res.status(500).send('Error connecting to the database: ' + error.message);

        return;

    }

    console.log('Inserting data:', { menu, price: priceDecimal, cardNumber });

    try {

        const query = `

            INSERT INTO transactions (transaction_date, menu, price, card_number)

            VALUES (CURDATE(), ?, ?, ?)

        `;

        const [result] = await pool.query(query, [menu, priceDecimal, cardNumber]);

        console.log('Query result:', result);

        res.status(200).send('Payment successful');

    } catch (error) {

        console.error('Error executing query:', error.message);

        res.status(500).send('Error executing query: ' + error.message);

    }

};

At the cloud sql instance, I got next instance: 

It has a public IP and even I've created a firewall rule in order to allo traffic between cloud function and cloud sql instance:  

hoever I'm getting next error in executing app: no coneccting to data base:                                                              

2024-07-19 18:37:16.543 CEST

POST500285 B10.1 sChrome 126 https://europe-southwest1-app-menu-427017.cloudfunctions.net/completePayment

2024-07-19 18:37:16.598 CEST

Database pool created successfully

2024-07-19 18:37:26.601 CEST

2024-07-19 18:37:16.543 CEST

POST500285 B10.1 sChrome 126 https://europe-southwest1-app-menu-427017.cloudfunctions.net/completePayment

2024-07-19 18:37:16.598 CEST

Database pool created successfully

2024-07-19 18:37:26.601 CEST

Error connecting to the database: connect ETIMEDOUT

Any suggestion - idea about the error origin? Thanks a lot

[ms4446]

To resolve the connect ETIMEDOUT error when connecting to a Cloud SQL instance from a Cloud Function, several troubleshooting steps and improvements can be undertaken. This error typically indicates a network timeout, suggesting that the Cloud Function is unable to connect to the Cloud SQL instance within the specified time.

First, verify the firewall rules. Ensure the source IP range in your firewall rule correctly matches the outgoing IP addresses of your Cloud Function. For testing purposes, you can allow traffic from 0.0.0.0/0, but for production, restrict this to specific IP ranges to enhance security. Also, confirm that the firewall rule allows TCP traffic on port 3306, which is the default port for MySQL.

Next, consider the network connectivity options. For enhanced security and reduced latency, it is recommended to use a private IP for your Cloud SQL instance. Ensure your instance is configured to use a private IP and set up a VPC connector to establish a secure connection between your Cloud Function’s network and the Cloud SQL instance’s network.

To test the connection from Cloud Shell, install the MySQL client and attempt to connect to your Cloud SQL instance using the private IP. This can help identify any networking issues that may be preventing the connection.

Optimizing the Cloud Function configuration is another crucial step. Increase the timeout setting to allow more time for the connection to establish. Enable minimum instances to reduce the impact of cold starts, ensuring that your function remains warm and ready to handle requests promptly.

Improving the code for the Cloud Function can also help address the issue. Wrap the connection pool creation and database query execution in try-catch blocks to provide more detailed error logging. This can offer insights into the exact cause of the timeout. Additionally, store sensitive data, such as database credentials and host information, in environment variables for better security.

Here’s a revised version of the Cloud Function code:

const mysql = require('mysql2/promise');

let pool;

exports.completePayment = async (req, res) => {
    res.set('Access-Control-Allow-Origin', '*');
    res.set('Access-Control-Allow-Methods', 'POST');
    res.set('Access-Control-Allow-Headers', 'Content-Type');

    if (req.method === 'OPTIONS') {
        res.status(204).send('');
        return;
    }

    const { menu, price, cardNumber } = req.body;

    if (!menu || !price || !cardNumber) {
        res.status(400).send('Missing data');
        return;
    }

    const priceDecimal = parseFloat(price);
    if (isNaN(priceDecimal)) {
        res.status(400).send('Invalid price');
        return;
    }

    if (!pool) {
        try {
            pool = await mysql.createPool({
                user: process.env.DB_USER,
                password: process.env.DB_PASS,
                database: process.env.DB_NAME,
                host: process.env.DB_HOST || '34.175.115.163',
                port: 3306,
                connectTimeout: 10000
            });
            console.log('Database pool created successfully');
        } catch (error) {
            console.error('Error creating database pool:', error);
            res.status(500).send(`Error connecting to the database: ${error.message}`);
            return;
        }
    }

    try {
        const connection = await pool.getConnection();
        console.log('Database connection successful');
        connection.release();
    } catch (error) {
        console.error('Error connecting to the database:', error.message);
        res.status(500).send('Error connecting to the database: ' + error.message);
        return;
    }

    console.log('Inserting data:', { menu, price: priceDecimal, cardNumber });

    try {
        const query = `
            INSERT INTO transactions (transaction_date, menu, price, card_number)
            VALUES (CURDATE(), ?, ?, ?)
        `;
        const [result] = await pool.query(query, [menu, priceDecimal, cardNumber]);
        console.log('Query result:', result);
        res.status(200).send('Payment successful');
    } catch (error) {
        console.error('Error executing query:', error.message);
        res.status(500).send('Error executing query: ' + error.message);
    }
};

[jaia]

Hello,

Thank you for your engagement regarding this issue. We haven’t heard back from you regarding this issue for sometime now. Hence, I'm going to close this issue which will no longer be monitored. However, if you have any new issues, Please don’t hesitate to create a new issue. We will be happy to assist you on the same.

Regards,
Jai Ade