In the <a target="_blank" rel="noopener noreferrer" href="https://deniapps.com/blog/mysql-rds-aborted-connection-error-aws-lambda">previous post</a>, we learned that to ultimately solve the issue of AWS Lambda overwhelming RDS instance is using Amazon RDS Proxy. In this post, we are looking into this.AWS provides some documents, we found the below two links are helpful:<ol><li><a target="_blank" rel="noopener noreferrer" href="https://aws.amazon.com/blogs/compute/using-amazon-rds-proxy-with-aws-lambda/">Using Amazon RDS Proxy with AWS Lambda</a></li><li><a target="_blank" rel="noopener noreferrer" href="https://www.youtube.com/watch?v=ULRnn6tIYu8">Introduction to RDS Proxy</a> (Youtube Video)</li></ol>The idea behind this is pretty simple. The flow that we had before was: &nbsp;Amazon API Gateway → Lambda function → Amazon RDS, like the diagram below:<figure class="image"><img src="https://d2908q01vomqb2.cloudfront.net/1b6453892473a467d07372d45eb05abc2031647a/2019/12/03/serverless-app-rds.png"><figcaption>Lambda to RDS Diagram by AWS Compute Blog</figcaption></figure>And then we would like to add an RDS Proxy between Lambda function and Amazon RDS, so then we could use RDS Proxy to share the database connection pool, improve database efficiency, application scalability, and security. &nbsp;The architecture looks like this:<figure class="image"><img src="https://d1.awsstatic.com/re19/Rialto/product-page-diagram_RDS%20Proxy_How-it-works@2x.a18916586f49718a16fd11579d168ab08c83d333.png"><figcaption>RDS Proxy &nbsp;by AWS</figcaption></figure><h3>Get Started</h3>Like most of AWS Services, the setup of RDS Proxy is painful. We hope you have an AWS Certified DevOps Engineer in your team. &nbsp;In this article, we are NOT going to cover the details of how to configure this which involves AWS Secrets Manager, IAM Role/Policy, and attaching a proxy to AWS Lambda. Please follow the links at the beginning of this article and good luck!We will go over some issues we had on the Lambda function side. Here is what we had before in dbConnection:<pre><code class="language-javascript">import getSecret from './secrets';

require('mysql');

const dbContext = require('knex')({
 client: 'mysql',
 connection: async () =&gt; {
 return {
 host: await getSecret(`${process.env.ENV}_DB_HOST`),
 user: await getSecret(`${process.env.ENV}_DB_USER`),
 password: await getSecret(`${process.env.ENV}_DB_PASSWORD`),
 database: await getSecret(`${process.env.ENV}_DB_NAME`),
 ssl: 'Amazon RDS'
 };
 },
 pool: {
 min: 0,
 max: 10,
 createTimeoutMillis: 30000,
 acquireTimeoutMillis: 30000,
 idleTimeoutMillis: 30000,
 reapIntervalMillis: 1000,
 createRetryIntervalMillis: 100,
 },
 debug: false
});

export default dbContext;</code></pre>Updated (10/19/2020) &nbsp;We removed the pool settings <code>propagateCreateError: false</code> (Sorry for the mistake, and it's should be <code>true</code>) &nbsp;Per Mikael (knex/tarn maintainer) :<blockquote>Please don't set <code>propagateCreateError: false</code> it will make knex behave badly. … it should never be touched</blockquote>And we need to point the connection to RDS Proxy instead of RDS, and since we use IAM authentication, we add RDS.singer to get the token. &nbsp;The code like this:<pre><code class="language-javascript">const getToken = (hostName, userName) =&gt; {
 const signer = new AWS.RDS.Signer({
 region: 'us-east-1',
 hostname: hostName,
 port: 3306,
 username: userName
 });

 const token = signer.getAuthToken({
 username: userName
 });
 return token;
};

const dbContext = require('knex')({
 client: 'mysql',
 const rdsProxyEndpoint = await getSecret(`${process.env.ENV}_Proxy_Endpoint`);
 const dbUser = await getSecret(`${process.env.ENV}_DB_USER`);
 const dbName = await getSecret(`${process.env.ENV}_DB_NAME`);

 const token = getToken(rdsProxyEndpoint, dbUser);
 const connectionConfig = {
 host: rdsProxyEndpoint,
 user: dbUser,
 database: dbName,
 ssl: { rejectUnauthorized: false },
 password: token,
 authSwitchHandler: ({ pluginName, pluginData }, cb) =&gt; {
 console.log('Setting new auth handler.');
 }
 };

 // Adding the mysql_clear_password handler
 connectionConfig.authSwitchHandler = (data, cb) =&gt; {
 if (data.pluginName === 'mysql_clear_password') {
 console.log('pluginName: ' + data.pluginName);
 const password = token + '\0';
 const buffer = Buffer.from(password);
 cb(null, password);
 }
 };
 ...
 }</code></pre>And then, we got an error in RDS log,&nbsp;<blockquote>INFO Error: MySQL is requesting the mysql_clear_password authentication method, which is not supported.&nbsp; &nbsp;at Handshake.AuthSwitchRequestPacket (/var/task/node_modules/mysql/lib/protocol/sequences/Handshake.js:47:17) …..</blockquote>Regarding MySQL Doc:<blockquote>Hashing or encryption cannot be done for authentication schemes that require the server to receive the password as entered on the client side. In such cases, the client-side <code>mysql_clear_password</code> plugin is used, which enables the client to send the password to the server as cleartext. There is no corresponding server-side plugin. Rather, <code>mysql_clear_password</code> can be used on the client side in concert with any server-side plugin that needs a cleartext password.</blockquote>Since AWS RDS Proxy is using <code>mysql_clear_password</code> plugin, we have to add this Auth Handler. But we use ‘mysql’ node npm, which does not yet support <code>mysql_clear_password</code>. The good news is that there is a better mysql node npm, ‘mysql2’ available.But we got second error:&nbsp;<blockquote>"String cannot represent value: { introText: "", breadcrumbFields: [{}] }"</blockquote>That's because we have some database columns are JSON type, and node <code>mysql2</code> library parses the JSON string to a native object, which is different in node <code>mysql</code> library. Since our code base were written based on node <code>mysql</code> library, it would be a lot of changes if using JSON object. The good thing is that <code>mysql2</code> allows <code>typeCast</code> to treat JSON type as String type. The modified code looks like this:<pre><code class="language-javascript">const connectionConfig = {
 host: rdsProxyEndpoint,
 user: dbUser,
 database: dbName,
 ssl: { rejectUnauthorized: false },
 password: token,
 authSwitchHandler: ({ pluginName, pluginData }, cb) =&gt; {
 console.log('Setting new auth handler.');
 },
 typeCast: (field, next) =&gt; {
 if (field.type === 'JSON') {
 return field.string();
 }
 return next();
 }
};</code></pre>After that we also got some other errors in RDS:<h4>Error1 - unable to get local issuer certificate</h4><blockquote>INFO Error: unable to get local issuer certificate&nbsp; &nbsp;at TLSSocket.&lt;anonymous&gt; (/var/task/node_modules/mysql/lib/Connection.js:317:48)</blockquote>That's because, during the debugging, we set ssl back to ‘Amazon RDS’, like <code>ssl: 'Amazon RDS'</code>. This means we should set <code>ssl: { rejectUnauthorized: false }</code> when pointing the connection to RDS Proxy.<h4>Error2 - Access denied for user … (using password: YES)</h4><blockquote>INFO Error: Access denied for user 'db_admin'@'10.0.3.240' (using password: YES)&nbsp; &nbsp;at Packet.asError (/var/task/node_modules/mysql2/lib/packets/packet.js:712:17)</blockquote>That's because, during the debugging, we set the connection host to be RDS endpoint instead of RDS Proxy endpoint.<h4>Error3 - Access denied for user … (using password: NO)</h4><blockquote>INFO Error: Access denied for user 'db_admin'@'%' (using password: NO)&nbsp; &nbsp;at Packet.asError (/var/task/node_modules/mysql2/lib/packets/packet.js:712:17)</blockquote>This error drove us crazy. We had gone thru all settings in AWS RDS Proxy to make sure they are the same as what AWS doc says, but had not made it work. &nbsp;Finally, we checked the RDS Proxy log (it's new log in addition to RDS log and Lambda log after using RDS Proxy) and saw this:<blockquote>Proxy authentication with IAM authentication failed for user "nccih_admin" with TLS on. Reason: The proxy couldn't authenticate using IAM. The expected "Credential's region" value is "us-east-1" but was "us-eat-1". Generate a new authentication token and use it in a new client connection.&nbsp;&nbsp;</blockquote>Oh no! There is a typo: ‘us-eat-1’ So lesson learned: &nbsp;check logs first (all available logs) it would help you!<h3>Last thing</h3>In the local we still use local mySQL server, so there is no RDS proxy involved, and we still use min:0/max:10 in the pool setting, but in the AWS, we now have RDS proxy for the connection pooling, so we should make min/max based on the total connections available in RDS. We are trying min:5/max:30 for now, but looks like it could be set to much higher numbers. &nbsp;Here is the final dbConnection.js:<pre><code class="language-javascript">import getSecret, { getToken } from './secrets';

require('mysql2');

// 8/26/2020
// LOCAL - no pool share b/w requests, so use 0 for min
// DEV/PORD - use rds proxy for pooling connecgtion, so use 5 for min

const minNum = process.env.ENV === 'local' ? 0 : 5;
const maxNum = process.env.ENV === 'local' ? 10 : 30;
const idleTimout = process.env.ENV === 'local' ? 1000 : 30000;

const dbContext = require('knex')({
 client: 'mysql2',
 connection: async () =&gt; {
 console.log('connecting to db...');
 if (process.env.ENV === 'local')
 return {
 host: '127.0.0.1',
 user: 'db_admin',
 password: 'db_pass',
 database: 'db_name',
 typeCast: (field, next) =&gt; {
 if (field.type === 'JSON') {
 return field.string();
 }
 return next();
 }
 };

 const rdsProxyEndpoint = await getSecret(`${process.env.ENV}_Proxy_Endpoint`);
 const dbUser = await getSecret(`${process.env.ENV}_DB_USER`);
 const dbName = await getSecret(`${process.env.ENV}_DB_NAME`);

 const token = getToken(rdsProxyEndpoint, dbUser);

 const connectionConfig = {
 host: rdsProxyEndpoint,
 user: dbUser,
 database: dbName,
 ssl: { rejectUnauthorized: false },
 password: token,
 authSwitchHandler: ({ pluginName, pluginData }, cb) =&gt; {
 console.log('Setting new auth handler.');
 },
 typeCast: (field, next) =&gt; {
 if (field.type === 'JSON') {
 return field.string();
 }
 return next();
 }
 };

 // Adding the mysql_clear_password handler
 connectionConfig.authSwitchHandler = (data, cb) =&gt; {
 if (data.pluginName === 'mysql_clear_password') {
 console.log('pluginName: ' + data.pluginName);
 const password = token + '\0';
 const buffer = Buffer.from(password);
 cb(null, password);
 }
 };

 console.log(connectionConfig);
 return connectionConfig;
 },
 pool: {
 min: minNum, 
 max: maxNum,
 createTimeoutMillis: 30000,
 acquireTimeoutMillis: 30000,
 idleTimeoutMillis: idleTimout,
 reapIntervalMillis: 1000,
 createRetryIntervalMillis: 100
 },
 debug: false
});

export default dbContext;</code></pre>That's it. Hope this helps.Updated (10/19/2020) &nbsp; We removed the pool settings <code>propagateCreateError: true. </code>Per Mikael (knex/tarn maintainer) -&nbsp;<blockquote>the setting, <code>propagateCreateError,</code> should never be touched.</blockquote>

In this article, we would like to share some issues we had and resolved during setting up AWS Lambda to use Amazon RDS Proxy.

Setup AWS Lambda to Use Amazon RDS Proxy

Recently, we noticed a ton of Errors in AWS CloudWatch Logs, the message like this:<blockquote>2020-08-21T17:43:32.142855Z 8812 [Note] Aborted connection 8812 to db: 'dbname' user: 'db_user' host: 'x.x.x.x' (Got an error reading communication packets)</blockquote>“Aborted connection" is a common MySQL communication error. &nbsp;The possible reasons are numerous includes:<ol><li>It takes more than connect_timeout seconds to obtain a connect packet.</li><li>The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server.</li><li>The max_allowed_packet variable value is too small or queries require more memory than you have allocated for mysqld.</li><li>The client program did not call mysql_close() before exiting.</li><li>The client program ended abruptly in the middle of a data transfer.</li><li>A connection packet does not contain the right information.</li><li>Any DNS related issues and the hosts are authenticated against their IP address instead of Hostnames.</li></ol>Yes, it's a long list, unfortunately.&nbsp;<h3>MySQL Tuning</h3>We went thru the list, the first thing we tried was increasing those three variables:<ol><li>max_allowed_packet = 500M</li><li>innodb_log_buffer_size = 32M</li><li>innodb_log_file_size = 2047M</li></ol>But they did not help, then we checked some timeout variables:<ol><li><code>wait_timeout</code> -this parameter refers to the number of seconds the server waits for activity on a non-interactive connection before closing it<ul><li>Allowed value: 1-31536000</li><li>The default value for MySQL 5.7: 28800</li></ul></li><li><code>connect_timeout</code> - this parameter the number of seconds that the server waits for a connect packet before responding with Bad handshake.<ul><li>Allowed value: 2- 31536000</li><li>The default value for MySQL 5.7:10</li></ul></li><li><code>interactive_timeout</code> - this parameter refers to the number of seconds the server waits for activity on an interactive connection before closing it.<ul><li>Allowed value: 1-31536000</li><li>The default value for MySQL 5.7: 28800</li></ul></li></ol>We have default values for all, so unlikely it is due to “The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server.”<h3>Application Troubleshooting</h3>It's enough for the MySQL tuning. We looked into the Application side. We use Lambda Serverless &nbsp;Apollo Server. The database connection is using KnexJS library. &nbsp;Our DB connection was something like this:<pre><code class="language-javascript">require('mysql');

const dbContext = require('knex')({
 client: 'mysql',
 connection: async () =&gt; {
 return {
 host: await getSecret(`${process.env.ENV}_DB_HOST`),
 user: await getSecret(`${process.env.ENV}_DB_USER`),
 password: await getSecret(`${process.env.ENV}_DB_PASSWORD`),
 database: await getSecret(`${process.env.ENV}_DB_NAME`),
 ssl: 'Amazon RDS'
 };
 },
 pool: {
 min: 2,
 max: 10, 
 createTimeoutMillis: 30000,
 acquireTimeoutMillis: 30000,
 idleTimeoutMillis: 30000,
 reapIntervalMillis: 1000,
 createRetryIntervalMillis: 100
 },
 debug: false
});

export default dbContext;</code></pre>Updated (10/19/2020) &nbsp; We removed the pool settings <code>propagateCreateError: true. </code>Per Mikael (knex/tarn maintainer) -&nbsp;<blockquote>the setting, <code>propagateCreateError,</code> should never be touched.</blockquote>There are many people having this issue on KnexJS GitHub issues channel (see the reference links below.) The key point here is that we are using serverless,&nbsp;<blockquote>Lambda functions are stateless, so there is no way to share a connection pool between functions. - rusher</blockquote>And our DB connection had pool setting as <code>min:2 / max: 10</code>. The good thing about connection pooling is that regarding wiki:<blockquote>In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database.</blockquote>The first expression for us was that since Lambda functions doe not share a connection pool, maintaining a connection pool becomes a waste here. Those connections sleep there do nothing, and they end up taking all the available connections. So as many people suggested, we changed the pool setting to be&nbsp;<pre><code class="language-javascript"> pool: {
 min: 1,
 max: 1, 
 ...</code></pre>Okay, after that, we saw some improvement. And we also did one thing:&nbsp;<pre><code class="language-javascript">const APIGatewayProxyHandler = (event, context, callback) =&gt; {
 context.callbackWaitsForEmptyEventLoop = false;</code></pre>By specifying <code>context.callbackWaitsForEmptyEventLoop = false</code>, we allow a DB connection to be maintained in a global variable in the lambda's container resulting in a faster connection.However, we noticed the performance issue after changing the connection pool to be <code>min:1/max:1</code>. Why? Did we just said, “Lambda functions doe not share a connection pool”? &nbsp;Let's be more clear, although Lambda functions do not share a connection pool, the queries in the same function do share a connection pool. For example, we have a GraphQL call, getCustomPages. When it's called, a DB connection is created, and then all queries in this call can share the connection pool, especially, those queries are written in an async way (i.e., with Promise) To demonstrate this, we can use MySQLWorkbench under Management/Client Connections. When the pool size is 1, we see only one connection created for one GraphQL call, but when the pool size is set to 2/10, we see more connections (about 10) are created for one call, and then we can see all queries are sharing those connections perfectly. We see page load time drops from ~10 seconds to a half-second by changing the connection pool from 1/1 to 2/10. That's because without sharing the pool, every query has to wait until another query finished before it can be executed. If a lambda function has thousands of queries in a loop, the big connection poll will truly boost the performance.&nbsp;So for better performance, we should still use the default pool connection. But because AWS Lambda functions do not share the pool properly, the pool connection will be open for each Lambda function call and end up using all of the available MySQL connections.&nbsp;After looking into <a target="_blank" rel="noopener noreferrer" href="https://github.com/vincit/tarn.js">tarn.js</a>, the library KnexJS used to manage the connection pool, we found out that the setting <code>idleTimeoutMillis</code> controls how many mill seconds before the free resources are destroyed. For example, if we set it to 30000, ie. 30 seconds, then the idle connections will be destroyed after 30 seconds. However, it will not destroy all sleeping connections, but keep the minimum number of connections set in the pool configuration. Since we cannot reuse the pool connection in the next Lambda function call, why not set the minimum to 0?Brilliant! The final solution we have is:<ul><li>Use the pool settings as: (Updated: 8/26/2020 - since the connections are not shared by next Lambda function call, we could set idelTiemoutMillis to be much shorter, and we changed it to 1000 from 30000.)</li></ul><pre><code class="language-javascript">pool: {
 min: 0,
 max: 10, 
 createTimeoutMillis: 30000,
 acquireTimeoutMillis: 30000,
 idleTimeoutMillis: 1000, //changed it from 30000 to 1000
 reapIntervalMillis: 1000,
 createRetryIntervalMillis: 100,
 propagateCreateError: true
 },</code></pre><ul><li>Set <code>context.callbackWaitsForEmptyEventLoop</code> to be <code>false</code> (Note: the DB connection outside of Lambda handler function)</li></ul><pre><code class="language-javascript">const APIGatewayProxyHandler = (event, context, callback) =&gt; {
 context.callbackWaitsForEmptyEventLoop = false;</code></pre><h3>The Ultimate Solution</h3>As we repeated many times above, the bottleneck of the AWS Lambda functions is that they don't share the resource. To better understand this, please check the following images:<figure class="image"><img src="https://images.deniapps.com/lambda-function-verwhelming-rds.png"><figcaption>Many concurrent connections kill Amazon RDS - Photo By <a target="_blank" rel="noopener noreferrer" href="https://blog.thundra.io/can-lambda-and-rds-play-nicely-together">thundra</a></figcaption></figure>Since each Lambda function is an individual process, it has to establish its connection to the DB instance. This design becomes very resource-intensive.Luckily, Amazon launched the preview of Amazon RDS Proxy in December 2019 and then made it generally available for both Mysql and PostgreSQL engines in<a target="_blank" rel="noopener noreferrer" href="https://aws.amazon.com/blogs/aws/amazon-rds-proxy-now-generally-available/"> Jun 2020</a>, which address this issue. We call it “the Ultimate Solution” here. :-)<blockquote>Amazon RDS Proxy allows applications to pool and share connections established with the database, improving database efficiency, application scalability, and security. RDS Proxy&nbsp;<a target="_blank" rel="noopener noreferrer" href="https://aws.amazon.com/blogs/database/improving-application-availability-with-amazon-rds-proxy/">reduces client recovery time</a>&nbsp;after failover by up to 79% for Amazon Aurora MySQL and by up to 32% for Amazon RDS for MySQL. Also, its authentication and access can be managed through integration with&nbsp;<a target="_blank" rel="noopener noreferrer" href="https://aws.amazon.com/secrets-manager">AWS Secrets Manager</a>&nbsp;and&nbsp;<a target="_blank" rel="noopener noreferrer" href="https://aws.amazon.com/iam">AWS Identity and Access Management</a>&nbsp;(IAM).</blockquote>Again, we borrow a beautiful diagram from <a target="_blank" rel="noopener noreferrer" href="https://blog.thundra.io/can-lambda-and-rds-play-nicely-together">Thundra</a>:<figure class="image"><img src="https://images.deniapps.com/lambda-function-with-rds-proxy.png"><figcaption>Many concurrent connections connect to Amazon RDS via RDS Proxy - Photo By <a target="_blank" rel="noopener noreferrer" href="https://blog.thundra.io/can-lambda-and-rds-play-nicely-together">thundra</a></figcaption></figure>Please follow the links to see how to set up Amazon RDS Proxy if you are interested. We are about the end of this long long post. Thank you for reading. &nbsp;Updated (08/27/2020): We added <a target="_blank" rel="noopener noreferrer" href="https://deniapps.com/blog/setup-aws-lambda-to-use-amazon-rds-proxy">AWS RDS Proxy</a>.References:<ul><li><a target="_blank" rel="noopener noreferrer" href="https://github.com/knex/knex/issues/1875">https://github.com/knex/knex/issues/1875</a> (How do I use Knex with AWS Lambda</li><li><a target="_blank" rel="noopener noreferrer" href="https://github.com/vincit/tarn.js">https://github.com/vincit/tarn.js</a> (Another Resource Pool)</li><li><a target="_blank" rel="noopener noreferrer" href="https://blog.thundra.io/can-lambda-and-rds-play-nicely-together">https://blog.thundra.io/can-lambda-and-rds-play-nicely-together</a> (Play Lambda and RDS Nicely Together)</li><li><a target="_blank" rel="noopener noreferrer" href="https://aws.amazon.com/blogs/aws/amazon-rds-proxy-now-generally-available/">https://aws.amazon.com/blogs/aws/amazon-rds-proxy-now-generally-available/</a> (AWS RDS Proxy)</li></ul>

Starting for AWS Cloudwatch logs, we spent a week looking into 'Aborted Connection" errors on RDS. Finally, we figured out that although Lambda functions do not share a connection pool, the queries in the same function do share a connection pool, so we should keep on using the pool, but we should set the pool with min 0, so it could be destroyed without taking space. And the ultimate solution is to use Amazon RDS.

All Posts In Rds Proxy