MySQL query failing with error "java.lang.IllegalStateException: Parameter at position 0 is not set" | Appsmith | Page 1

hard flicker Apr 27, 2023, 7:59 PM

#

A query that I have been using for a while is now failing to execute. I do not think I have changed anything with it.I have self-hosted docker container w/ Community v1.9.16.

Having trouble figuring out what this means or how to debug further. Any ideas?

"actionId":"62aa450a2677176b434f38c2"
"requestedAt":"2023-04-27 15:50:30"
"requestParams":{
"Query":{
"value":"BEGIN; INSERT INTO media (media_type, entry_datetime, discovery_notes, short_list) SELECT c.id, NOW(), "", IF(true = true , IF(false = true, 0, 1) , 0) FROM categories c LEFT JOIN category_types ct ON ct.id = c.category_type_id WHERE (c.category = "book") and (ct.category_type = "media type"); SELECT @media_id := MAX(id) from media; INSERT INTO books (media_id, title, year, author_id, country_id, entry_datetime, fiction, history, reference, collection) VALUES (@media_id, "All Systems REd", 2017, 22000328, 21000001, NOW(), IF(true = true , 1 , 0), IF(false = true , 1 , 0), IF(false = true , 1 , 0), IF(false = true , 1 , 0)); INSERT INTO categories_media (media_id, category_id, entry_datetime) VALUES ((SELECT max(id) FROM media) , 10000015, NOW()); COMMIT;"
}
}

frosty mountainBOT Apr 27, 2023, 8:05 PM

#

Hey There,

We've registered your query, and our team will get back to you soon.

Regards,
Pranav

frosty mountainBOT Apr 27, 2023, 8:55 PM

#

Hey there,
Thank you for reaching out to the Appsmith support team. We've registered your
query, and our team is reviewing it.
In the meantime, to help us debug the issue, please consider following steps and
provide us the information:

A loom.com recording of this issue.
When did this issue start happening? Was it an update?
Context on what is this query doing. There seems to be a lot of moving parts
here and it's hard to follow.

Best regards,
Jimmy H.

hard flicker Apr 27, 2023, 9:26 PM

#

Hi @ebon valley - The last time I used this query successfully was 12/30/22. I hadn't used it since then until a few weeks ago. When I tried I got this error. I am guessing an update happened in that window that affected this but I'm not sure how to track that down. If you can show me how to get a log of updates I will provide. Happy to do a loom but since there is some private data I'd prefer not to share publicly. Possible to send a link privately?

frosty mountainBOT Apr 28, 2023, 5:36 PM

#

Yes, you can send an email to [email protected]. Please use the title of this
thread for the subject and include a link in the email so we can link it to this
thread internally.

hard flicker Apr 29, 2023, 12:55 PM

#

Done, thank you

frosty mountainBOT May 1, 2023, 7:19 PM

#

Hey! We've received your email and are looking into this.

frosty mountainBOT May 12, 2023, 9:02 PM

#

Hi, could you add [email protected] to your workspace to review this more
quickly and closely? Furthermore, if possible, do I have your approval to run
test queries with this access?

hard flicker May 16, 2023, 2:16 AM

#

Done, and yes feel free to run queries. Please use the app called "Book Portal"

#

Thanks!

frosty mountainBOT May 16, 2023, 6:36 PM

#

No problem! We will look into this.

frosty mountainBOT May 16, 2023, 7:37 PM

#

@hard flicker Could you please provide a link to the Book Portal app?
We're having trouble finding it. Please make sure that you've invited
[email protected] as a developer to the app or workspace the app is in.

hard flicker May 18, 2023, 10:08 PM

#

Apologies @frosty mountain - just seeing now. I just sent the link on the support thread. this is a self hosted version.

frosty mountainBOT May 18, 2023, 10:15 PM

#

In this case, could you please try something for me? Could you disable "Use
Prepared Statement," execute the query again, and show me the Logs screen,
please?

#

hard flicker May 20, 2023, 7:03 PM

#

@frosty mountain - Use Prepared Statement has been disabled. I have a dynamic binding with a JS Object that returns text with SQL keywords.

#

Here is the log screen with Prep. Stat. disabled, which shows the error that is at the top of this thread:

#

frosty mountainBOT May 22, 2023, 9:54 PM

#

Thank you, could you please share with me the Errors tab?

#

hard flicker May 24, 2023, 3:54 AM

#

Apologies, yes, here you go.

#

frosty mountainBOT May 29, 2023, 4:37 PM

#

Gentle reminder.

hard flicker Jun 1, 2023, 4:05 AM

#

@frosty mountain I have provided the error screenshot above

frosty mountainBOT Jun 1, 2023, 2:19 PM

#

@hard flicker Yes, but we need to see the errors tab of that info pane
to get more context from the error.

hard flicker Jun 2, 2023, 1:28 PM

#

@remote violet this screenshot is the errors tab. I realize I am not on the edit query page, but it is the error from the query. Is that what you are looking for?

#

#1101236321185452043 message

frosty mountainBOT Jun 2, 2023, 2:51 PM

#

@hard flicker Thank you for this! Apologies for the confusion. Our
integration was down for some time last week and we missed that image.

hard flicker Jun 2, 2023, 4:46 PM

#

No problem @frosty mountain . Also, let me know if you are having trouble accessing the app that I shared with you. You should feel free to manipulate the app as it is a clone of my production app and is tied to a cloned db for debug purposes.

frosty mountainBOT Jun 2, 2023, 5:06 PM

#

Thanks!

frosty mountainBOT Jun 5, 2023, 8:01 PM

#

@hard flicker Could you please send your server logs? You can use the
existing email thread related to this ticket. Let us know here when you send
them, and we will check.

hard flicker Jun 9, 2023, 5:58 PM

#

@remote violet - done

frosty mountainBOT Jun 9, 2023, 6:07 PM

#

@hard flicker Are you available for a call regarding this issue?

hard flicker Jun 9, 2023, 6:11 PM

#

I am

frosty mountainBOT Jun 9, 2023, 6:12 PM

#

Please stand by. I'll create the meeting.

hard flicker Jun 9, 2023, 6:13 PM

#

OK, I am actually getting pulled into a work meeting

#

15 minutes ok?

frosty mountainBOT Jun 9, 2023, 6:15 PM

#

Yes, that's fine. Let me know when your meeting is over. I'll ping you here in
15 min.

hard flicker Jun 9, 2023, 6:25 PM

#

ok thank you, ready now

frosty mountainBOT Jun 9, 2023, 6:29 PM

#

@hard flicker
https://us05web.zoom.us/j/87315811631?pwd=ZXVhcmdpYTZjZlZ6V0E5MjFQSDduQT09

Zoom Video

Join our Cloud HD Video Meeting

Zoom is the leader in modern enterprise video communications, with an easy, reliable cloud platform for video and audio conferencing, chat, and webinars across mobile, desktop, and room systems. Zoom Rooms is the original software-based conference room solution used around the world in board, conference, huddle, and training rooms, as well as ex...

frosty mountainBOT Jun 9, 2023, 6:44 PM

#

@hard flicker Thank you for your assistance! We will continue to
investigate this matter.

hard flicker Jun 9, 2023, 7:22 PM

#

Thanks @remote violet - FYI, I removed all of the other apps from that workspace, so the only one in there is where you can reproduce the issue.

frosty mountainBOT Jun 9, 2023, 7:56 PM

#

Thank you! I'll pass this along to the team.

frosty mountainBOT Jun 12, 2023, 5:56 PM

#

Hello, it appears that the error was caused by a colon in one of your SELECT
queries inside "add book." The part of the query you had was:

SELECT @media_id := MAX(id) FROM media;

and I changed it to:

SELECT MAX(id) AS media_id FROM media;

Both queries function the same, and now your "add book" query works flawlessly.

Additionally, I opened a bug report for this, and you can track it at this link:
https://github.com/appsmithorg/appsmith/issues/24381

I am awaiting confirmation from your end to validate that the problem has been
resolved.

GitHub

[Bug]: Queries containing colons `:` cannot be executed in MySQL. ·...

Is there an existing issue for this? I have searched the existing issues Description Queries that contain colons : cannot be executed in MySQL because they will always result in the error: java.lan...

hard flicker Jun 19, 2023, 2:08 AM

#

That did the trick! Thank you for figuring it out. This workaround seems to work for me 👍

#MySQL query failing with error "java.lang.IllegalStateException: Parameter at position 0 is not set"