Solved: MySQL PROTOCOL_CONNECTION_LOST Error with AWS CDK CustomResource, Lambda

I am using AWS CDK for defining AWS infrastructure.
I have database-stack that defines RDS(MySQL) instance and have set up AWSCustomResource like below to run table-creation.sql when the Database instance is deployed for the first time.

Everytime, the instance is launched, the table is not created in the instance showing error:

2023-06-11T14:23:19.813Z	8155569d-cd7e-4fc4-833c-db7da6afbf3d	INFO	Error: Connection lost: The server closed the connection.
    at Protocol.end (/var/task/index.js:9350:17)
    at Socket.<anonymous> (/var/task/index.js:9907:32)
    at Socket.<anonymous> (/var/task/index.js:10233:14)
    at Socket.emit (node:events:525:35)
    at Socket.emit (node:domain:489:12)
    at endReadableNT (node:internal/streams/readable:1358:12)
    at processTicksAndRejections (node:internal/process/task_queues:83:21)
    --------------------
    at Protocol._enqueue (/var/task/index.js:9374:52)
    at Protocol.handshake (/var/task/index.js:9304:27)
    at PoolConnection.connect (/var/task/index.js:9925:22)
    at Pool.getConnection (/var/task/index.js:10330:20)
    at Pool.query (/var/task/index.js:10447:12)
    at /var/task/index.js:10997:22
    at new Promise (<anonymous>)
    at Runtime.handler (/var/task/index.js:10996:18)
    at processTicksAndRejections (node:internal/process/task_queues:96:5) {
  fatal: true,
  code: 'PROTOCOL_CONNECTION_LOST'
}

To solve this, I tried looking into this post which seemed relevant: https://stackoverflow.com/questions/20210522/nodejs-mysql-error-connection-lost-the-server-closed-the-connection
But the solution didn't work for me and other posts as well.

Also I noticed the solutions were mostly with NodeJS without AWS Lambda functions.
I tried many things and I am wondering what could be the possible problems and how to solve this issue. Any advice would be thankful.

Here are the AWS CDK defined database-stack.ts with AWSCustomResource to run a lambda function at its creation and rds-init.ts which is lambda function code for creating tables.

database-stack.ts

// Create Lambda Function to initialize the DB with tables and data.
        const rdsLambdaFunction = new NodejsFunction(this, "rdsLambdaFN", {
            entry: "./src/lambda_functions/rds-init.ts",
            runtime: Runtime.NODEJS_16_X,
            timeout: Duration.minutes(3), // Preventing coldstart time
            functionName: "rds-init-function",
            environment: {
                DB_ENDPOINT_ADDRESS: dbProxy.endpoint,
                DB_NAME: "vegafoliodb",
                DB_SECRET_ARN: instance.secret?.secretFullArn || "", // Not Fetching Password directly but via SecretARN for security :)
            },
            vpc,
            vpcSubnets: vpc.selectSubnets({
                subnetType: ec2.SubnetType.PRIVATE_WITH_EGRESS,
            }),
            bundling: {
                // Use Command Hooks to include table-creation.sql file in the bundle
                commandHooks: {
                    beforeBundling(inputDir: string, outputDir: string) {
                        return [
                            `cp ${inputDir}/src/lambda_functions/table-creation.sql ${outputDir}`,
                        ];
                    },

                    afterBundling(inputDir: string, outputDir: string) {
                        return [];
                    },

                    beforeInstall(inputDir: string, outputDir: string) {
                        return [];
                    },
                },

                externalModules: [
                    "aws-sdk", // No Need to include AWS SDK as we are using native aws-sdk.
                ],
            },
            securityGroups: [dbSG],
        });

// Custom Resource to invoke Lambda Function on db stack creation creation
        new AwsCustomResource(this, "rdsInitCustomResource", {
            onCreate: {
                service: "Lambda",
                action: "invoke",
                parameters: {
                    FunctionName: rdsLambdaFunction.functionArn,
                    InvocationType: "RequestResponse",
                },
                physicalResourceId: PhysicalResourceId.of(
                    "rdsInitCustomResource"
                ),
            },
            policy: AwsCustomResourcePolicy.fromSdkCalls({
                resources: [rdsLambdaFunction.functionArn],
            }),
            role: customResourcerole,
        });

rds-init.ts

// Custom Resource to invoke Lambda Function on db stack creation creation
        new AwsCustomResource(this, "rdsInitCustomResource", {
            onCreate: {
                service: "Lambda",
                action: "invoke",
                parameters: {
                    FunctionName: rdsLambdaFunction.functionArn,
                    InvocationType: "RequestResponse",
                },
                physicalResourceId: PhysicalResourceId.of(
                    "rdsInitCustomResource"
                ),
            },
            policy: AwsCustomResourcePolicy.fromSdkCalls({
                resources: [rdsLambdaFunction.functionArn],
            }),
            role: customResourcerole,
        });

Placing the Lambda in the same Security Group as the Database does not automatically allow the connection between them. To do so, you need to allow connections within the SG explicitly. You can do so like this:

myDb.connections.allowDefaultPortInternally();

I noticed this was due to the missing dependency code I have to define for AwsCustomResource

The code needs to make sure it's ran after the creation of the database instance(dbproxy if you have)

// run rdsCustomResource after creation (Add dependency)
rdsCustomResource.node.addDependency(dbProxy);
rdsCustomResource.node.addDependency(instance);

I added two lines and working like a charm.