andr04.•15mo ago

MySQL script: invalid type: string "", expected a boolean

I'm new with Windmill and trying execute a simple request from a connected MySQL resource:

-- ? offset (int) = 0
-- ? limit (int) = 100
SELECT id from tariff LIMIT ?,?

-- ? offset (int) = 0
-- ? limit (int) = 100
SELECT id from tariff LIMIT ?,?

But I get the following result:

{
    "error": {
        "name": "ExecutionErr",
        "message": "error during execution of the script:\ninvalid type: string \"\", expected a boolean"
    }
}

{
    "error": {
        "name": "ExecutionErr",
        "message": "error during execution of the script:\ninvalid type: string \"\", expected a boolean"
    }
}

What I do wrong?

15 Replies

rubenf•15mo ago

hey, not sure but shouldn't it be:

SELECT id from tariff LIMIT ? OFFSET ?

SELECT id from tariff LIMIT ? OFFSET ?

also I think you have inverted your parameters even if it doesn't matter

andr04.•15mo ago

No, the resulting SQL should be

SELECT id from tariff LIMIT 0,100

SELECT id from tariff LIMIT 0,100

On the same time, Explore schema works and shows correct tables and its field types, that means resource is configured correctly. I'm confusing... 🤯

rubenf•15mo ago

investigating

rubenf•15mo ago

i'm not able to reproduce

rubenf•15mo ago

what does your resource look like as a json ? it looks as if the ssl field is set as "" instead of true/false

andr04.•15mo ago

You're absolutely correct. I don't know why it was set to the wrong value, because I used UI to configure the resource. And schema exploring works even with a wrong config.

rubenf•15mo ago

interesting it's likely a bug, do you have any way to reproduce this reliably ? actually it does this everytime I will fix, thank you for the report! found, it's an issue in the resource type

andr04.•15mo ago

The next problem is BigInt of id field. I'm looking for a way to interpret it as string. I know I can cast it, but maybe it's more correct way?

rubenf•15mo ago

you will have to use mysql functions using mysql/cast is the most correct way. Cast your string to bigint

andr04.•15mo ago

No, I say about a conceptual BigInt problem with JavaScript. Actually, it is int64, but JS supports only int53, as a result the ID is rounding to some other ID.

rubenf•15mo ago

I think you will have to cast your bigint to a string

andr04.•15mo ago

It's interesting fact, but Chrome Developer Tools in raw response shows the correct (not rounded IDs). I really recommend you on the backend transform bigint to string before send the result. And the code https://hub.windmill.dev/scripts/mysql/577/execute-query-mysql returns an object with rows and fields properties, and there the field id is interpreted as string. Will try to find a way to protect it from SQL injections.

rubenf•15mo ago

Why not cast in your query in the meantime ?

andr04.•15mo ago

Conceptually, it looks like a workaround. It's a bigint, not string, and transforming it to string should be done on another layer just for JS, which https://hub.windmill.dev/scripts/mysql/577/execute-query-mysql does. I have other more complicated queries which will be difficult to synchronize, because they work good outside JS.

rubenf•15mo ago

are you using json and JS interchangeably ? the devtool give you back full bigint because the json format support infinitely large number, it's the js that indeed does not support it as you mention so technically, the backend behavior is somewhat correct to not cast it to string given that the output format support it now is that very practical given that it's likely gonna be consumed by js, probably not but it's not as clear cut as you mention it and casting it in the mysql is not a bad separation of concerns