Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Using lock and fetch throws "No value specified for parameter 7" sql exception #190

Closed
BukhariH opened this issue Mar 23, 2021 · 5 comments
Closed

Using lock and fetch throws "No value specified for parameter 7" sql exception #190

BukhariH opened this issue Mar 23, 2021 · 5 comments
Labels
bug
Milestone
10.1

Comments

@BukhariH
Copy link
Contributor

BukhariH commented Mar 23, 2021 •
edited
Loading

Hey @kagkarlsson!

Hope you're well - using your fantastic library at my new place and I saw that version 10.0 includes the pollUsingLockAndFetch so wanted to test it out.

Anyway, end up getting a weird exception only in production:

org.postgresql.util.PSQLException: No value specified for parameter 7.
    at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:270)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:297)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:148)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
    at com.github.kagkarlsson.jdbc.JdbcRunner.lambda$execute$2(JdbcRunner.java:85)
    at com.github.kagkarlsson.jdbc.JdbcRunner.withConnection(JdbcRunner.java:140)
    at com.github.kagkarlsson.jdbc.JdbcRunner.execute(JdbcRunner.java:66)
    at com.github.kagkarlsson.jdbc.JdbcRunner.query(JdbcRunner.java:62)
    at com.github.kagkarlsson.scheduler.jdbc.PostgreSqlJdbcCustomization.lockAndFetch(PostgreSqlJdbcCustomization.java:58)
    at com.github.kagkarlsson.scheduler.jdbc.AutodetectJdbcCustomization.lockAndFetch(AutodetectJdbcCustomization.java:87)
    at com.github.kagkarlsson.scheduler.jdbc.JdbcTaskRepository.lockAndGetDue(JdbcTaskRepository.java:157)
    at com.github.kagkarlsson.scheduler.LockAndFetchCandidates.run(LockAndFetchCandidates.java:75)
    at com.github.kagkarlsson.scheduler.RunUntilShutdown.run(RunUntilShutdown.java:40)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
    at java.lang.Thread.run(Thread.java:832)
com.github.kagkarlsson.jdbc.SQLRuntimeException: org.postgresql.util.PSQLException: No value specified for parameter 7.
    at com.github.kagkarlsson.jdbc.JdbcRunner.translateException(JdbcRunner.java:126)
    at com.github.kagkarlsson.jdbc.JdbcRunner.lambda$execute$2(JdbcRunner.java:93)
    at com.github.kagkarlsson.jdbc.JdbcRunner.withConnection(JdbcRunner.java:140)
    at com.github.kagkarlsson.jdbc.JdbcRunner.execute(JdbcRunner.java:66)
    at com.github.kagkarlsson.jdbc.JdbcRunner.query(JdbcRunner.java:62)
    at com.github.kagkarlsson.scheduler.jdbc.PostgreSqlJdbcCustomization.lockAndFetch(PostgreSqlJdbcCustomization.java:58)
    at com.github.kagkarlsson.scheduler.jdbc.AutodetectJdbcCustomization.lockAndFetch(AutodetectJdbcCustomization.java:87)
    at com.github.kagkarlsson.scheduler.jdbc.JdbcTaskRepository.lockAndGetDue(JdbcTaskRepository.java:157)
    at com.github.kagkarlsson.scheduler.LockAndFetchCandidates.run(LockAndFetchCandidates.java:75)
    at com.github.kagkarlsson.scheduler.RunUntilShutdown.run(RunUntilShutdown.java:40)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.util.concurrent.FutureTask.run(FutureTask.java:264)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
    at java.lang.Thread.run(Thread.java:832)

Here's my config:

        Scheduler
                .create(configureDataSource(dataSource))
                .tableName(TABLE_NAME)
                .pollingInterval(Duration.ofMillis(50))
                .heartbeatInterval(Duration.ofSeconds(30))
                .shutdownMaxWait(Duration.ofMinutes(1))
                .threads(THREADS)
                .executorService(executor)
                .startTasks(toTasks(jobs))
                .pollUsingLockAndFetch(0.5, 1.0)
                .build();

build.gradle

    implementation 'com.github.kagkarlsson:db-scheduler:10.0'
    implementation 'org.postgresql:postgresql:42.2.19'

I'm running postgres version 12.5 on AWS RDS.

From the stacktrace it seems to be throwing an error here:

db-scheduler/db-scheduler/src/main/java/com/github/kagkarlsson/scheduler/jdbc/PostgreSqlJdbcCustomization.java

Lines 47 to 70 in 1d9139c

@Override
public List<Execution> lockAndFetch(JdbcTaskRepositoryContext ctx, Instant now, int limit) {
final JdbcTaskRepository.UnresolvedFilter unresolvedFilter = new JdbcTaskRepository.UnresolvedFilter(ctx.taskResolver.getUnresolved());
String selectForUpdateQuery =
" UPDATE "+ctx.tableName+" st1 SET picked = ?, picked_by = ?, last_heartbeat = ?, version = version + 1 " +
" WHERE (st1.task_name, st1.task_instance) IN (" +
" SELECT st2.task_name, st2.task_instance FROM "+ctx.tableName+" st2 " +
" WHERE picked = ? and execution_time <= ? " + unresolvedFilter.andCondition() + " order by execution_time asc FOR UPDATE SKIP LOCKED LIMIT ?)" +
" RETURNING st1.*";
return ctx.jdbcRunner.query(selectForUpdateQuery,
ps -> {
// Update
ps.setBoolean(1, true); // picked (new)
ps.setString(2, truncate(ctx.schedulerName.getName(), 50)); // picked_by
setInstant(ps, 3, now); // last_heartbeat
// Inner select
ps.setBoolean(4, false); // picked (old)
setInstant(ps, 5, now); // execution_time
ps.setInt(6, limit); // limit
},
ctx.resultSetMapper.get());
}

I re-read the query a few times and as far as I can tell there seems to be six params there so I'm not sure sure where postgres is getting the 7th param from?

Anyway - would be great to get it working so let me know if you need anything else from my side!
@kagkarlsson
Copy link
Owner

kagkarlsson commented Mar 24, 2021 via email

@kagkarlsson
Copy link
Owner

kagkarlsson commented Mar 24, 2021 •
edited
Loading

It is caused by the UnresolvedFilter that attempts to filter out executions that exist in the database table, but your Scheduler instance have no mapping for. That filter adds some parameters which are never set (a bug).
Could be resolved short-term for you by removing that execution in the table, but I think this PR should do the trick: #191
Will try to get it released asap

@BukhariH
Copy link
Contributor Author

Thanks so much for the quick response Karl!

Deleted the unresolved task then deployed lock & fetch and it's all working very smoothly!

We are very heavy pollers and our CPU usage on the DB has dropped by half after releasing the lock & fetch strategy.

Thanks so much for all the hard work you put in to developing this library for us all!

@kagkarlsson
Copy link
Owner

Very good to hear that the new polling strategy had such a significant effect! Thank you for taking time and giving feedback 🙇

@kagkarlsson
Copy link
Owner

Fix released in v10.1

@kagkarlsson kagkarlsson added this to the 10.1 milestone Mar 24, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug
Projects
None yet
Milestone
10.1
Development

No branches or pull requests
2 participants
@kagkarlsson @BukhariH