Autoreload is disabled because the development mode is off

[koxudaxi]

Autoreload is disabled because the development mode is off

[chaiiplss]

Thanks, I'll try this out and let you know!

[koxudaxi]

@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.

[chaiiplss]

@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.

[koxudaxi]

@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
            }
        ]
    ]
}

[chaiiplss]

@koxudaxi So this is how I am trying to run the query.

• I am using this package to connect to the local mysql server.
• Then, I am using the data-api-client package and running my query using that instead. I am not running it on cli.

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.

[koxudaxi]

@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.

sample.ts

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()

Result

$ 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 }
  ]
}