Fork me on GitHub
#sql
<
2023-05-09
>
lepistane18:05:52

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

Cam Saul18:05:54

Can you do a simple query like SELECT 1?

Cam Saul18:05:21

So it only fails when you do CREATE VIEW?

Cam Saul18:05:30

What DB are we talking about too? Postgres?

lepistane18:05:33

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'
breaks

lepistane19:05:03

select * from fixture_view_v2 -> returns all of the data no problem

Cam Saul19:05:23

The error is "connection refused"? And it happens consistently?

lepistane19:05:49

i can copy stack trace, give me just a second. Yes. Every time i use sport in where clause it breaks

Cam Saul19:05:15

I wonder if "connection refused" is a red herring since clearly you can connect and fetch results

lepistane19:05:30

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

lepistane19:05:20

i see (first time i hear about red herring btw) So how/where/what to look for/into

Cam Saul19:05:27

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?

Cam Saul19:05:34

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

lepistane19:05:45

"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

Cam Saul19:05:18

Can you do

SELECT * FROM fixture_view_v2 WHERE sport IS NOT NULL
?

Cam Saul19:05:52

And can you query the table sport comes from directly? SELECT * FROM some_table WHERE sport = 'NBA'?

Cam Saul19:05:49

I'm wondering if this could be some sort of permissions issue

lepistane19:05:55

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

lepistane19:05:35

i've ran all migrations and everything from the same account/credentials

Cam Saul19:05:57

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?

lepistane19:05:35

is it possible that this free tier instance is weak and maybe memory runs out because the query is 'hairy'? sport is type text

Cam Saul19:05:03

Maybe? You should be able to look at the memory usage in the AWS web interface for RDS

lepistane19:05:16

it seems to me nothing is crazy

Cam Saul19:05:53

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

lepistane19:05:48

i am gonna try now

lepistane19:05:38

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

lepistane19:05:06

before that i did

mysql> SELECT * FROM fixture_view_v2 where stream_name = 'NBA 1'
....
42 rows in set (0.10 sec)

lepistane19:05:55

so it's something weird 😕

Cam Saul19:05:08

Can you do DESCRIBE fixture_view_v2 to see if it gives us any useful info about sport?

lepistane19:05:12

field type null ... sport text yes ...

Cam Saul20:05:40

hmmm, I don't know. Maybe that theory about being to hairy could be the explanation

lepistane08:05:19

sport was type text which mariadb had problems with, something with buffers when searching over it

lepistane08:05:25

thanks for the help @U42REFCKA