MySQL queries in railway Query Tab giving Application Error/Syntax Error | Railway | Page 1

rustic canyon Apr 10, 2023, 6:38 AM

#

Project ID: 49284993-6bdd-4798-a68a-6e1c796e5fa2

Trying to do a simple SELECT query using the Query tab in Railway. Every time I run something like SELECT * FROM groups it either gives me a syntax error, or I get redirected to a new page saying Application Error, with hundreds of browser console warnings. I can't even get my data to display through the Data tab, either, which is also giving me a syntax error somehow. Really frustrated with this. Hope someone can help me out here.

gleaming adderBOT Apr 10, 2023, 6:38 AM

#

Project ID: 49284993-6bdd-4798-a68a-6e1c796e5fa2

#

It may be a bug with MySQL on Railway. It has been raised to the team.

rustic canyon Apr 10, 2023, 6:38 AM

#

#

#

worldly oracle Apr 10, 2023, 6:41 AM

#

yeah the database stuff needs some tlc, just use something like dbgate or mysql workbench to perform queries

rustic canyon Apr 10, 2023, 6:44 AM

#

Well I couldn't seem to do it through my local environment either... I just get a ER_PARSE_ERROR whenever I try to run queries that I know work whenever I am connecting to my local database. Do you think this may be related?

worldly oracle Apr 10, 2023, 6:45 AM

#

no that would something else completely unrelated to the railway ui

rustic canyon Apr 10, 2023, 6:46 AM

#

I mean with my railway database being unable to parse simple queries...

worldly oracle Apr 10, 2023, 6:46 AM

#

what software are you using to connect to your database

rustic canyon Apr 10, 2023, 6:47 AM

#

Using mysql2 NPM package

#

The base mysql package didn't seem to work either

worldly oracle Apr 10, 2023, 6:48 AM

#

can you send me the code snippet of the createConnection function you use when connecting to a database on railway?

rustic canyon Apr 10, 2023, 6:49 AM

#

#

connectionConfig object looks fine too...

#

worldly oracle Apr 10, 2023, 6:51 AM

#

rustic canyon

this is a printout of the object, correct?

rustic canyon Apr 10, 2023, 6:51 AM

#

Yeah

worldly oracle Apr 10, 2023, 6:51 AM

#

rustic canyon

I don't see a port variable being used?

rustic canyon Apr 10, 2023, 6:53 AM

#

Only in my index.js server file

#

worldly oracle Apr 10, 2023, 6:53 AM

#

no a port for the mysql database

worldly oracle Apr 10, 2023, 6:54 AM

#

rustic canyon

but that's good, I commend you for using the railway provided PORT

rustic canyon Apr 10, 2023, 6:54 AM

#

Yeah, I don't have that in my mysql index file for establishing the connection

worldly oracle Apr 10, 2023, 6:54 AM

#

rustic canyon Yeah, I don't have that in my mysql index file for establishing the connection

kinda necessary I would think?

rustic canyon Apr 10, 2023, 6:54 AM

#

Or elsewhere in that file

#

Hmm

#

Oh

#

It is included in the MYSQL_URL env variable though

#

Which falls back to it if if the node env is in dev

worldly oracle Apr 10, 2023, 6:55 AM

#

rustic canyon It is included in the MYSQL_URL env variable though

yeah but I don't see that being used

rustic canyon Apr 10, 2023, 6:56 AM

#

#

It's part of process.env.MYSQL_URL

#

Which includes the 5644 port number

worldly oracle Apr 10, 2023, 6:57 AM

#

and when you run this app locally, you're running it either through railway run or in a railway shell

rustic canyon Apr 10, 2023, 6:58 AM

#

I haven't done that yet. If I'm running locally, I'll just connect using my local MySQL server running from XAMPP

#

And at that point the mysqlURL variable isn't being used, and instead createPool will use the connection object I passed in

#

Which, I guess would make the env variables in there redundant

worldly oracle Apr 10, 2023, 6:59 AM

#

okay well try running queries on your railway database with something like dbgate

rustic canyon Apr 10, 2023, 7:02 AM

#

Alright. I'll see if I have any issues with that. I'll report back if I do. Really appreciate your time. Thanks

worldly oracle Apr 10, 2023, 7:03 AM

#

if that returns issues, we can try restarting the database, and if that doesn't work I'll flag for team review

rustic canyon Apr 10, 2023, 7:18 AM

#

Would this be correct for the "Server" field when connecting via DbGate?

worldly oracle Apr 10, 2023, 7:29 AM

#

you can just use the url to connect

rustic canyon Apr 10, 2023, 7:30 AM

#

Oh in the SSH tab yeah?

worldly oracle Apr 10, 2023, 7:31 AM

#

no

#

i can't give you directions as I'm not on my computer right now

rustic canyon Apr 10, 2023, 7:32 AM

#

Gotcha

worldly oracle Apr 10, 2023, 7:33 AM

#

I think they're a drop-down somewhere that says something about using a url

rustic canyon Apr 10, 2023, 7:40 AM

#

I see radio buttons to toggle using database URL using certain connection types, if I select OracleDB/PostgreSQL/Redis amongst some others. but nothing like that with MySQL selected as the connection type

#

Host and port, and Socket connections are the only ones I'm seeing in the dropdown with MySQL selected

worldly oracle Apr 10, 2023, 7:40 AM

#

okay then just copy all the variables in

rustic canyon Apr 10, 2023, 7:43 AM

#

Hm... That's kinda why I asked about the server variable being correct or not. I'm unable to connect, unless I did something wrong elsewhere

#

worldly oracle Apr 10, 2023, 7:44 AM

#

why is there an h at the beginning of server

#

and what options do you see under connection mode

rustic canyon Apr 10, 2023, 7:45 AM

#

Oh, probably accidentally copied over the h flag from where I copied it. Same issue though.

#

Trying to look for it

#

Oh

#

Host and Port

#

Or Socket

worldly oracle Apr 10, 2023, 7:46 AM

#

what error are you getting?

rustic canyon Apr 10, 2023, 7:46 AM

#

It's kinda vague...

#

Error
at e.exports (C:\Users\brend\AppData\Local\Programs\dbgate\resources\app.asar\packages\api\dist\bundle.js:11:86455)
at processTicksAndRejections (node:internal/process/task_queues:96:5)
at async process.<anonymous> (C:\Users\brend\AppData\Local\Programs\dbgate\resources\app.asar\packages\api\dist\bundle.js:456:324963)

Error JSON: {}

Connection: {
"server": "",
"engine": "mysql@dbgate-plugin-mysql",
"sshMode": "userPassword",
"sshKeyfile": "",
"useSshTunnel": true,
"sshHost": "",
"sshPort": "",
"sshLogin": "",
"sshPassword": "",
"unsaved": true,
"_id": "",
"authType": "hostPort",
"useDatabaseUrl": "",
"databaseUrl": "",
"port": "5644",
"user": "",
"password": "",
"defaultDatabase": "",
"displayName": "***"
}

Platform: win32

worldly oracle Apr 10, 2023, 7:48 AM

#

what does the little blurb besides the colours say

rustic canyon Apr 10, 2023, 7:49 AM

#

Blurb?

#

The display name?

#

"railway-reddit-clone"

worldly oracle Apr 10, 2023, 7:49 AM

#

no, to the right of the colours

rustic canyon Apr 10, 2023, 7:49 AM

#

Or underneath it

#

Oh, nothing

worldly oracle Apr 10, 2023, 7:49 AM

#

full screenshot please

rustic canyon Apr 10, 2023, 7:50 AM

#

worldly oracle Apr 10, 2023, 7:50 AM

#

do /beta here

rustic canyon Apr 10, 2023, 7:50 AM

#

And earlier is the error I would get when I click show detail

worldly oracle Apr 10, 2023, 7:52 AM

#

worldly oracle do /beta here

^

rustic canyon Apr 10, 2023, 7:52 AM

#

Yep, did that

worldly oracle Apr 10, 2023, 7:52 AM

#

go back to railway, open your database plugin, open the command pallet, the search for restart, and click it

rustic canyon Apr 10, 2023, 7:54 AM

#

#

Like, within here?

#

Not seeing it

worldly oracle Apr 10, 2023, 7:54 AM

#

yes that is your database plugin

worldly oracle Apr 10, 2023, 7:54 AM

#

rustic canyon Not seeing it

screenshot

rustic canyon Apr 10, 2023, 7:55 AM

#

worldly oracle Apr 10, 2023, 7:55 AM

#

open the command pallet

rustic canyon Apr 10, 2023, 7:56 AM

#

I can't seem to find it, which tab is it in?

worldly oracle Apr 10, 2023, 7:56 AM

#

it's ctrl + k

rustic canyon Apr 10, 2023, 7:57 AM

#

Oh okay, got it

#

So just wait now? Or should it have already happened within the minute. Can't tell if it did anything

worldly oracle Apr 10, 2023, 7:58 AM

#

just wait several minutes before trying anything

rustic canyon Apr 10, 2023, 7:58 AM

#

Alright

#

Well...

#

Doesn't seem like anything got fixed. I swear I clicked it lol

worldly oracle Apr 10, 2023, 8:04 AM

#

okay will flag team

#

sorry, scratch that, can't flag team on mobile for some reason, will do it in the morning

rustic canyon Apr 10, 2023, 8:05 AM

#

No worries. I really appreciate your time trying to help

#

Thanks ^^

worldly oracle Apr 10, 2023, 8:06 AM

#

no problem 🙂

quick flicker Apr 10, 2023, 9:30 AM

#

I think I know why this happens.. groups is a reserved keyword in MySQL from v8 and above. If you try to use it without escaping (closing it in backticks, like groups (two ` - god I hate Discord's formatting..) it's going to throw you a syntax error)

#

For the connection error, try setting the connection mode to URL and use the MYSQL_URL variable in your Railway MySQL's plugin

quick flicker Apr 10, 2023, 9:35 AM

#

quick flicker I think I know why this happens.. `groups` is a reserved keyword in MySQL from v...

I'm not sure how you managed to create a table named groups in the first place lol, it's probably some ORM you're using that's automagically escaping reserved keywords.. Honestly I'd say rename your table and don't use reserved keywords, but if you want to, make sure you always escape it by enclosing it with backticks when referencing it

edit: or maybe it's just escaping every value, which I guess is the sanest behaviour 🤷‍♂️

I'll follow up on this!

quick flicker Apr 10, 2023, 9:45 AM

#

worldly oracle sorry, scratch that, can't flag team on mobile for some reason, will do it in th...

I gotchu.

rustic canyon Apr 10, 2023, 7:23 PM

#

quick flicker I think I know why this happens.. `groups` is a reserved keyword in MySQL from v...

Perhaps that wasn't the best example, but I did test it with other tables and got the same thing. I just ran a query seconds ago SELECT * FROM post_votes and it gave me an application error:

#

rustic canyon Apr 10, 2023, 7:24 PM

#

quick flicker For the connection error, try setting the connection mode to URL and use the `MY...

There isn't a connection mode for URL. It's either "Host and port" or "Socket"

#

#

Using DbGate

worldly oracle Apr 10, 2023, 7:24 PM

#

let's move away from tests using the railway built in database query UI please

rustic canyon Apr 10, 2023, 7:24 PM

#

Couldn't find anything like that within mysqlworkbench either

#

Just resonding to rc, but okay

#

Nevermind, forgot there was a URL connection option in mysql workbench. Tried it last night but still had the "h" in the beginning of the url 🤦‍♂️ it works now

worldly oracle Apr 10, 2023, 7:33 PM

#

I did have a sneaking suspicion that it was user error

rustic canyon Apr 10, 2023, 7:34 PM

#

Honestly, all this deployment stuff has been driving me nuts lol. Something like this would have happened sooner or later

#

Well guess it's up to me to figure out the rest now that the database stuff is resolved. Thanks again guys.

chrome knoll Apr 26, 2023, 4:36 PM

#

I also get redirected to a new page with Application error: a client-side exception has occurred (see the browser console for more information).. I can reproduce this by deploying a new MySQL database, creating a new table "MyTable" (but name doesn't seem to matter) with the default settings (column name: "id", type: "serial", no default, constraint: "Primary Key"), then adding a new row to this table with the default (id: 1) , then switching to the Query tab to execute select * from MyTable;, at which point the error is thrown. Upon inspecting the console (in Firefox), I see many instances of TypeError: l is undefined, followed by NextJS errors Error rendering page: Error: Cancel rendering route, followed by a response object with code: "INTERNAL_SERVER_ERROR", status: 404, and message: "Project not found”, among other things. In Safari I just get TypeError: undefined is not an object (evaluating 'l.find’) and the redirect to the Application error page. In my brief testing, this seems to happen with any query executed through the web UI against any table of any MySQL database in any project, and regardless of table relation schema or contents. Here's one of my project IDs: 4ef30e0b-f908-4f22-989d-eb50d20fb1a8

worldly oracle Apr 26, 2023, 4:43 PM

#

chrome knoll I also get redirected to a new page with `Application error: a client-side excep...

please dont rely on the data tab in the plugin

chrome knoll Apr 26, 2023, 4:48 PM

#

worldly oracle please dont rely on the data tab in the plugin

Do you mean the Query tab? The Data tab is where the list of tables appears, and where you can add tables or click through into a table to add or edit rows. For production use I won't be doing ClickOps in the UI, but this is a bit jarring for prospective Railway users just checking out the platform.

quick flicker Apr 26, 2023, 4:55 PM

#

hey! Sorry about this. Can you give me your OS & Browser (+versions)?

worldly oracle Apr 26, 2023, 4:57 PM

#

chrome knoll Do you mean the Query tab? The Data tab is where the list of tables appears, and...

my bad, i do actually mean both data and query tabs, they are both not very reliable, but i hear thats gonna be improved

chrome knoll Apr 26, 2023, 4:58 PM

#

worldly oracle my bad, i do actually mean both data and query tabs, they are both not very reli...

Good to hear!

#MySQL queries in railway Query Tab giving Application Error/Syntax Error