You are correct - these queries are not coming from jOOQ at all - the queries that were causing the issues were generated by JDBC during the setting up of the connection. jOOQ itself behaves correctly, the logs were confusing so I appreciate you taking a look into this. For the record I have jdbc version mysql-connector-java-8.0.7-dmr ( Revision: d33a3ca56530848bebdf75cf48cf8e68e0feef98 ) There isn't a whole lot of prior work done (at least published that I could find) on doing offline cacheing with java and proxysql unfortunately. Thanks again.
On Friday, September 29, 2017 at 12:13:17 AM UTC-7, Lukas Eder wrote: > > jOOQ doesn't run any such statements for you, it doesn't even manage your > connection... > > 2017-09-29 0:33 GMT+02:00 <[email protected] <javascript:>>: > >> Sorry for the double post. I've been investigating further after >> understanding some of the dynamics between these two pieces of software. >> The issue isn't actually with the inlined sql (I was assuming the error was >> in executing that statement) but in some initial setup that is being done >> by some part of the tool chain (not sure where)- specifically >> >> SET character_set_results = NULL >> SET autocommits=? >> >> These (obviously) not possible to be cache. The rest of the query is >> working as intended. I am not sure at what layer of the toolchain these >> commands are added though. >> Is this from jOOQ? >> Thanks for taking the time to look. >> >> On Thursday, September 28, 2017 at 1:53:21 PM UTC-7, [email protected] >> wrote: >>> >>> I am passing my queries through proxy sql which caches the raw query >>> strings its forwarding to mysql. >>> Proxysql is seeing this as the rendered query and is then ignoring the >>> cache. >>> >>> On Tuesday, September 26, 2017 at 12:53:36 AM UTC-7, Lukas Eder wrote: >>>> >>>> Hello, >>>> >>>> How did you get this query string? I cannot reproduce this issue. The >>>> bind variable gets correctly inlined. All of your flags are correct API >>>> usage. >>>> >>>> Thanks, >>>> Lukas >>>> >>>> 2017-09-26 0:57 GMT+02:00 <[email protected]>: >>>> >>>>> I am using the latest jOOQ 3.9.5. >>>>> I went through the 7 step tutorial as provided on the main website. >>>>> Configuration: mysql running on 6033, basic credentials (this is a >>>>> dummy database that I setup specifically for this task) >>>>> >>>>> DB Schema is as follows >>>>> >>>>> CREATE DATABASE `library`; >>>>> >>>>> USE `library`; >>>>> >>>>> CREATE TABLE `author` ( >>>>> `id` int NOT NULL, >>>>> `first_name` varchar(255) DEFAULT NULL, >>>>> `last_name` varchar(255) DEFAULT NULL, >>>>> PRIMARY KEY (`id`) >>>>> ); >>>>> >>>>> >>>>> >>>>> >>>>> I generated the backing code using the default settings - and the >>>>> program works >>>>> import org.jooq.*; >>>>> import org.jooq.conf.ParamType; >>>>> import org.jooq.conf.Settings; >>>>> import org.jooq.conf.StatementType; >>>>> import org.jooq.impl.DSL; >>>>> >>>>> import static library.Tables.*; >>>>> import static org.jooq.impl.DSL.*; >>>>> >>>>> >>>>> import java.sql.*; >>>>> >>>>> public class JooqTutorial { >>>>> public static void main(String[] args) { >>>>> String username = "root"; >>>>> String password = "root"; >>>>> String url = "jdbc:mysql://127.0.0.1:6033/library"; >>>>> >>>>> try (Connection conn = DriverManager.getConnection(url, >>>>> username, password)) { >>>>> Settings settings = new Settings(); >>>>> settings.setStatementType(StatementType.STATIC_STATEMENT); >>>>> settings.setParamType(ParamType.INLINED); >>>>> DSLContext create = DSL.using(conn, SQLDialect.MYSQL, >>>>> settings); >>>>> SelectConditionStep<Record> fred = >>>>> create.select().from(AUTHOR).where( >>>>> AUTHOR.FIRST_NAME.eq(inline("fred")) >>>>> ); >>>>> Result<Record> result = fred.fetch(); >>>>> for (Record r : result) { >>>>> Integer id = r.getValue(AUTHOR.ID); >>>>> String firstName = r.getValue(AUTHOR.FIRST_NAME); >>>>> String lastName = r.getValue(AUTHOR.LAST_NAME); >>>>> System.out.printf("Id:%d %s, %s\n", id, lastName, >>>>> firstName); >>>>> } >>>>> } catch (Exception e) { >>>>> e.printStackTrace(); >>>>> } >>>>> } >>>>> } >>>>> >>>>> However, the code is not respecting the inline setting. Instead the >>>>> following statement is being generated and sent to the db >>>>> select `library`.`author`.`id`, `library`.`author`.`first_name`, >>>>> `library`.`author`.`last_name` from `library`.`author` where >>>>> `library`.`author`.`first_name` = ? >>>>> >>>>> >>>>> I know I have technically redundant settings and calls to inline, but >>>>> I figure at least one of them should have resulted in the query being >>>>> sent >>>>> 'inline' and not as a prepared statement. I am not sure why it isn't >>>>> inlining the value of 'fred' as described in the documentation. >>>>> >>>>> >>>>> >>>>> -- >>>>> You received this message because you are subscribed to the Google >>>>> Groups "jOOQ User Group" group. >>>>> To unsubscribe from this group and stop receiving emails from it, send >>>>> an email to [email protected]. >>>>> For more options, visit https://groups.google.com/d/optout. >>>>> >>>> >>>> -- >> You received this message because you are subscribed to the Google Groups >> "jOOQ User Group" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
