We are migrating the SP (Stored Proc) to new version. SP have statement as SELECT LAST_INSERT_ID() As Id.
Now when we run the migration on the database to move it to latest, everything in the SP is good except the above mentioned select statement.
SELECT LAST_INSERT_ID() As Id changes to SELECT 0 As Id.
We have a lambda function, which takes care of creating and/or upgrading the database to the latest migration.
When we call this lambda to create new database as well as to upgrade existing database from local environment (serverless-offline) i works fine.
When we call this lambda (deployed on the AWS) this behaves differently,
when we create database, it works fine, when trying to upgrade an existing database, it converts the LAST_INSERT_ID() to 0.
We have enabled debugging in the knex, In the logs it looks all find, but when we check the SP in the database, LAST_INSERT_ID() is converted to 0.
Observations
- we are not doubting on the AWS infra side, as it works fine when we create a new database.
- even the script is all fine, as it also works fine for other cases.
- not doubting on the database user permission, as it works again fine for other cases.
- even when we call the lambda with debugging enabled, which is deployed in AWS, logs looks as expected.
We are using the raw function to Drop and create the SP.
More details are added in the bug ticket in the github repo https://github.com/knex/knex/issues/5259