This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2023-05-09
Channels
- # announcements (3)
- # beginners (61)
- # biff (20)
- # cider (13)
- # clerk (6)
- # clojure (58)
- # clojure-brasil (5)
- # clojure-europe (30)
- # clojure-nl (1)
- # clojure-norway (10)
- # clojure-uk (5)
- # clr (25)
- # core-async (2)
- # cursive (19)
- # datahike (5)
- # datalevin (1)
- # docker (1)
- # emacs (3)
- # fulcro (4)
- # hoplon (3)
- # hyperfiddle (91)
- # java (2)
- # juxt (5)
- # london-clojurians (1)
- # lsp (38)
- # malli (12)
- # nrepl (9)
- # off-topic (7)
- # polylith (15)
- # portal (49)
- # rdf (2)
- # re-frame (43)
- # releases (2)
- # shadow-cljs (30)
- # spacemacs (15)
- # sql (36)
- # tools-build (20)
- # xtdb (3)
First time this happens to me. I've created a view that is created from a select that does joins on 10 tables. Locally it works without a problem. When i deploy to to AWS RDS db.t4g.micro free tier query is not getting executed and i am getting 'connection refused' error. How do i go about fixing it or debugging it? I can't recreate the problem the problem
view creation goes well. The problem is when i try to create queries using the view for example:
SELECT * FROM fixture_view_v2
where stream_name = 'NBA 1'
works
SELECT * FROM fixture_view_v2
where stream_name = 'NBA 1' and start_date > <today>
also works
but
SELECT * FROM fixture_view_v2
where sport = 'NBA'
breaksi can copy stack trace, give me just a second. Yes. Every time i use sport in where clause it breaks
I wonder if "connection refused" is a red herring since clearly you can connect and fetch results
mariadb
jkiss.dbeaver.model.impl.jdbc.JDBCException: SQL Error [08000]: Socket fail to connect to host:address=(host=abc)(port=3306)(type=primary). Connection refused (Connection refused)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCConnectionImpl.prepareStatement(JDBCConnectionImpl.java:197)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCConnectionImpl.prepareStatement(JDBCConnectionImpl.java:1)
at org.jkiss.dbeaver.model.DBUtils.createStatement(DBUtils.java:1438)
at org.jkiss.dbeaver.model.DBUtils.makeStatement(DBUtils.java:1404)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:558)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:491)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:498)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:917)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3794)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5061)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLNonTransientConnectionException: Socket fail to connect to host:address=(host=<>)(port=3306)(type=primary). Connection refused (Connection refused)
at org.mariadb.jdbc.client.impl.ConnectionHelper.connectSocket(ConnectionHelper.java:136)
at org.mariadb.jdbc.client.impl.StandardClient.<init>(StandardClient.java:103)
at org.mariadb.jdbc.Driver.connect(Driver.java:70)
at org.mariadb.jdbc.Driver.connect(Driver.java:101)
at org.mariadb.jdbc.Driver.connect(Driver.java:27)
at org.jkiss.dbeaver.model.impl.jdbc.JDBCDataSource.lambda$0(JDBCDataSource.java:206)
at org.jkiss.dbeaver.model.impl.jdbc.JDBCDataSource.lambda$1(JDBCDataSource.java:225)
at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:96)
at org.jkiss.dbeaver.model.impl.jdbc.JDBCDataSource.openConnection(JDBCDataSource.java:223)
at org.jkiss.dbeaver.ext.mysql.model.MySQLDataSource.openConnection(MySQLDataSource.java:448)
at org.jkiss.dbeaver.model.impl.jdbc.JDBCExecutionContext.connect(JDBCExecutionContext.java:103)
at org.jkiss.dbeaver.model.impl.jdbc.JDBCExecutionContext.connect(JDBCExecutionContext.java:88)
at org.jkiss.dbeaver.model.impl.jdbc.JDBCExecutionContext.getConnection(JDBCExecutionContext.java:201)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCConnectionImpl.getOriginal(JDBCConnectionImpl.java:60)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCConnectionImpl.prepareStatement(JDBCConnectionImpl.java:244)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCConnectionImpl.prepareStatement(JDBCConnectionImpl.java:146)
... 15 more
Caused by: java.net.ConnectException: Connection refused (Connection refused)
at java.base/java.net.PlainSocketImpl.socketConnect(Native Method)
at java.base/java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
at java.base/java.net.AbstractPlainSocketImpl.connect(Unknown Source)
at java.base/java.net.SocksSocketImpl.connect(Unknown Source)
at java.base/java.net.Socket.connect(Unknown Source)
at org.mariadb.jdbc.client.impl.ConnectionHelper.connectSocket(ConnectionHelper.java:130)
... 30 more
yeah based on that stack trace it looks like it just doesn't like that query, the question is why... are you sure that view has a sport
column?
when you call .prepareStatement()
the JDBC driver will parse the SQL and usually throw an Exception if there is something wrong with the query, before you even call .execute()
"CREATE VIEW fixture_view_v2 AS
select
f.fixture_id,
f.external_id,
count(nmv.match_id) matches_played,
f.start_date,
t.tournament_id,
t.name as tournament_name,
t.sport,
....
yes
i can see correct sport when i do select * from fixture_view_v2
And can you query the table sport
comes from directly? SELECT * FROM some_table WHERE sport = 'NBA'
?
SELECT * FROM fixture_view_v2 WHERE sport IS NULL
no results
SELECT * FROM fixture_view_v2 WHERE sport IS NOT NULL
returns all fixtures (count is = )
select * from tournament t
where sport = 'NBA'
⢠yes it returns expected result interesting. So WHERE sport = 'NBA'
doesn't work, but sport IS NULL
does. I think it's not a perms issue then. What's the column type of sport
?
is it possible that this free tier instance is weak and maybe memory runs out because the query is 'hairy'? sport is type text
Maybe? You should be able to look at the memory usage in the AWS web interface for RDS
Have you tried connecting with a CLI tool like mysql
or something and running the query? If you did that then we could determine whether it's a genuine problem with the query itself or if something weird is going on with JDBC and the code around it
SELECT * FROM fixture_view_v2 where stream_name = 'NBA 1' and sport = 'NBA';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on <HOST> (111)
ERROR:
Can't connect to the server
before that i did
mysql> SELECT * FROM fixture_view_v2 where stream_name = 'NBA 1'
....
42 rows in set (0.10 sec)
@U42REFCKA âď¸
Can you do DESCRIBE fixture_view_v2
to see if it gives us any useful info about sport
?
sport was type text which mariadb had problems with, something with buffers when searching over it
thanks for the help @U42REFCKA