Open chaiiplss opened 3 years ago
@chaiiplss
Thank you for creating this issue.
I can change the option to on
.
But, Why did you want auto-reload is enabled?
I guess the app will not be changed.
@koxudaxi So this would be the source of the issue which I could pinpoint. When I tried to create tables and populating them with the script that I wrote using data-api-client, script started giving errors w.r.t connection to mysql server.
I checked with data-api-client, there was no change in the version over there. Even when connection somehow worked, query was not parsed correctly. (Not sure why that happened)
Example:
Test table -
CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));
INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');
Query -
SELECT first_name,
age,
gender,
@curRank := IF(@curAge IS NULL, @curRank + 1 , 1) AS rank
FROM person p, (SELECT @curRank := 0, @curAge := null) r
ORDER BY age;
Following error comes -
Database error code: 1064. Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL= IF(@curAge IS NULL, @curRank + 1, 1) AS rank
I am also not sure what change was present in the latest release which started giving me this error. Any help would be appreciated.
@chaiiplss
I'm sorry for my late reply.
I have tested your query with awscli.
I can't get the error.
I use the latest version (0.6.6
) local-data-api
Your problem may came from your client library. How did you run the query?
$ aws rds-data execute-statement --database 'test' --resource-arn $RDS_DATA_API_CLIENT_RESOURCE_ARN \
--secret-arn $RDS_DATA_API_CLIENT_SECRETARN \
--sql $'SELECT first_name,
age,
gender,
@curRank := IF(@curAge IS NULL, @curRank + 1 , 1) AS rank
FROM person p, (SELECT @curRank := 0, @curAge := null) r
ORDER BY age;' --endpoint-url http://127.0.0.1:8080
{
"numberOfRecordsUpdated": 0,
"records": [
[
{
"stringValue": "Kathy"
},
{
"longValue": 18
},
{
"stringValue": "F"
},
{
"doubleValue": 1.0
}
],
[
{
"stringValue": "Jane"
},
{
"longValue": 20
},
{
"stringValue": "F"
},
{
"doubleValue": 2.0
}
],
[
{
"stringValue": "Nick"
},
{
"longValue": 22
},
{
"stringValue": "M"
},
{
"doubleValue": 3.0
}
],
[
{
"stringValue": "Bob"
},
{
"longValue": 25
},
{
"stringValue": "M"
},
{
"doubleValue": 4.0
}
],
[
{
"stringValue": "Anne"
},
{
"longValue": 25
},
{
"stringValue": "F"
},
{
"doubleValue": 5.0
}
],
[
{
"stringValue": "Jack"
},
{
"longValue": 30
},
{
"stringValue": "M"
},
{
"doubleValue": 6.0
}
],
[
{
"stringValue": "Bill"
},
{
"longValue": 32
},
{
"stringValue": "M"
},
{
"doubleValue": 7.0
}
],
[
{
"stringValue": "Steve"
},
{
"longValue": 36
},
{
"stringValue": "M"
},
{
"doubleValue": 8.0
}
]
]
}
@koxudaxi So this is how I am trying to run the query.
This is the config that I have used in data-api-client
. I don't think there is any issue with data-api-client side because there last version update was on 16 Dec 2020. And this error started coming recently only.
const data_api = require('data-api-client')({
...(IS_OFFLINE
? {
secretArn: AURORA_SECRET_OFFLINE,
resourceArn: AURORA_RESOURCE_OFFLINE,
database: AURORA_DB_NAME_OFFLINE,
sslEnabled: false,
keepAlive: false,
options: {
endpoint: 'localhost:8080'
}
}
: {
secretArn: AURORA_SECRET,
resourceArn: AURORA_RESOURCE,
database: AURORA_DB_NAME
})
})
If you don't think there is any issue from this package. I'll look more into this issue and post at the relevant place.
@chaiiplss
I just tested your SQL with data-api-client
.
It works on local-data-api and real AWS DataAPI.
Also, I use this docker-compose.yml
I recommend using the latest version (0.6.7
) which is fixed few bugs.
I show you the test script and the result.
import Client = require('data-api-client');
const data='DROP TABLE IF EXISTS person;\n' +
'CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));\n' +
'INSERT INTO person VALUES (1, \'Bob\', 25, \'M\');\n' +
'INSERT INTO person VALUES (2, \'Jane\', 20, \'F\');\n' +
'INSERT INTO person VALUES (3, \'Jack\', 30, \'M\');\n' +
'INSERT INTO person VALUES (4, \'Bill\', 32, \'M\');\n' +
'INSERT INTO person VALUES (5, \'Nick\', 22, \'M\');\n' +
'INSERT INTO person VALUES (6, \'Kathy\', 18, \'F\');\n' +
'INSERT INTO person VALUES (7, \'Steve\', 36, \'M\');\n' +
'INSERT INTO person VALUES (8, \'Anne\', 25, \'F\');'
// @ts-ignore
async function f() {
let connection = Client({
secretArn: 'arn:aws:secretsmanager:us-east-1:123456789012:secret:dummy',
resourceArn: 'arn:aws:rds:us-east-1:123456789012:cluster:dummy',
database: 'test',
region: 'us-east-1',
sslEnabled: false,
keepAlive: false,
options: {
endpoint: 'localhost:8080'
}
})
for (const sql of data.split('\n')) {
const res = await connection.query(sql)
console.log(sql,'\n', res)
}
const res = await connection.query('' +
'SELECT first_name,\n' +
' age,\n' +
' gender,\n' +
' @curRank := IF(@curAge IS NULL, @curRank + 1 , 1) AS rank\n' +
'FROM person p, (SELECT @curRank := 0, @curAge := null) r\n' +
'ORDER BY age;')
console.log('select...','\n', res)
}
f().then()
$ ts-node sample.ts
DROP TABLE IF EXISTS person;
{ numberOfRecordsUpdated: 0 }
CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));
{ numberOfRecordsUpdated: 0 }
INSERT INTO person VALUES (1, 'Bob', 25, 'M');
{ numberOfRecordsUpdated: 1 }
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
{ numberOfRecordsUpdated: 1 }
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
{ numberOfRecordsUpdated: 1 }
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
{ numberOfRecordsUpdated: 1 }
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
{ numberOfRecordsUpdated: 1 }
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
{ numberOfRecordsUpdated: 1 }
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
{ numberOfRecordsUpdated: 1 }
INSERT INTO person VALUES (8, 'Anne', 25, 'F');
{ numberOfRecordsUpdated: 1 }
select...
{
records: [
{ first_name: 'Kathy', age: 18, gender: 'F', rank: 1 },
{ first_name: 'Jane', age: 20, gender: 'F', rank: 2 },
{ first_name: 'Nick', age: 22, gender: 'M', rank: 3 },
{ first_name: 'Bob', age: 25, gender: 'M', rank: 4 },
{ first_name: 'Anne', age: 25, gender: 'F', rank: 5 },
{ first_name: 'Jack', age: 30, gender: 'M', rank: 6 },
{ first_name: 'Bill', age: 32, gender: 'M', rank: 7 },
{ first_name: 'Steve', age: 36, gender: 'M', rank: 8 }
]
}
Thanks, I'll try this out and let you know!
Describe the bug With
v0.6.0
and above, running thedocker run
command gives the following issue -To Reproduce
Expected behavior (Successful with
v0.5.8
)