I'm trying to update a table and set several column values but the query works half the time.
I'm really baffled because I don't understand why since the results of the Update query indicate that the number of rows changed is 1 (as expected).
I'm using a Connection Pool to handle my connection since I'm in a serverless environment (AWS Lambda + RDS).
The code itself is this :
async function mySQLConnectionPool() {
await provideConnectionInfo()
let pool = mysql.createPool({
connectionLimit: 20,
host: mySQLHost,
user: mySQLUser,
password: mySQLPassword,
database: mySQLDatabase
}
)
return pool;
}
api.post('/database-manager/gitlab-webhook', async (gitlabRequest, gitlabResponse) => {
if (connectionPool == null) {
connectionPool = await mySQLConnectionPool();
}
console.log("receiving messages from gitlab webhook")
await getPipelineIdsAndJobIds();
for (let index in pipelineAndJobIds) {
pipelineId = pipelineAndJobIds[index].pipeline_id;
jobId = pipelineAndJobIds[index].job_id
if (gitlabRequest.body["object_attributes"].id == pipelineId && gitlabRequest.body["object_attributes"].status == 'success') {
config = {
method: 'GET',
url: `https://gitlab.com/api/v4/projects/${PROJECT_ID_CREATE}/jobs/${jobId}/trace`,
headers: {
"PRIVATE-TOKEN": PRIVATE_TOKEN
}
}
//Get Job Logs using Job Id
await axios(config)
.then(
(getJobLogsRequest) => {
let jobLog = JSON.stringify(getJobLogsRequest.data)
const regex = new RegExp('.*instance-link = (.*.com:[0-9]{4}).*');
ec2InstanceLink = jobLog.match(regex)[1]
}
)
console.log(`ec2InstanceLink : ${ec2InstanceLink}`)
connectionPool.getConnection(function (err, connection) {
let query = "UPDATE ec2_request SET ?, request_status = 'Finished' WHERE pipeline_id = ?"
connection.query(query, [{instance_link: ec2InstanceLink}, pipelineId], function (err, result) {
if (err) {
throw err
connection.release();
} else {
connection.release();
console.log("EC2 link provided")
}
}
)
}
)
}
}
})
The result from the connection callback prove a changed row :
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 0,
serverStatus: 34,
warningCount: 0,
message: '(Rows matched: 1 Changed: 1 Warnings: 0',
protocol41: true,
changedRows: 1
}
If someone sees a mistake in my code, I'd be grateful because this error is driving me mad. I thought perhaps one of the parameters might be null, but turns they're all initialised.
Thanks a lot in advance.