Clojurians Log v2

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

thanks for the help @camsaul

Can you do a simple query like SELECT 1?

yes

So it only fails when you do CREATE VIEW?

What DB are we talking about too? Postgres?

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' 

SELECT * FROM fixture_view_v2
where stream_name = 'NBA 1' and start_date > <today>

SELECT * FROM fixture_view_v2
where sport = 'NBA'

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

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

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

Postgres?

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

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

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,
....

Can you do

SELECT * FROM fixture_view_v2 WHERE sport IS NOT NULL

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

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

SELECT * FROM fixture_view_v2 WHERE sport IS NULL 

SELECT * FROM fixture_view_v2 WHERE sport IS NOT NULL

select * from tournament t 
where sport = 'NBA'

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

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

it seems to me nothing is crazy

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

i am gonna try now

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)

so it's something weird 😕

@camsaul ☝️

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

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

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