Question about the TIME binding

[pqab]

we are going to insert time 00:00 to database, but sometimes the data is incorrect after inserted to database.

we have LocalTime in the entity

@Column(name = "time")
private LocalTime time;

the insert statement from hibernate.show_sql log

insert into `TABLE` (`time`) values (?)

the bindings from the hibernate type trace log

[BasicBinder.java] - binding parameter [1] as [TIME] - [00:00]

but when the data inserted to the database, the data changed randomly, for example

id	time
1	-838:59:59
2	00:00:20
3	00:00:00
4	00:02:57

May I know is there anything do after the binding and any ideas about it?

Environment

• Version: 1.0.0.CR6
• Database: ProxySQL 5.5.30 with MySQL 5.7.25-log

[DavideD]

It seems to work fine with MySQL.

I've tried to test it with ProxySQL but couldn't connect to it. Maybe you can tell me how to start it with docker so that I can run tests on it?

I'm not familiar with ProxySQL and I might not be able to check this until next week.

[pqab]

Thanks for checking, I will need some time to setup the ProxySQL with docker, I will share with you later

[pqab]

Below example to setup the ProxySQL with docker

docker-compose.yml

version: '3.0'
services:
  mysql:
    ......
  proxysql:
    image: proxysql/proxysql
    restart: always
    volumes:
      - "./proxysql.cnf:/etc/proxysql.cnf"
    ports:
      - "6033:6033"
      - "6032:6032"

proxysql.cnf

datadir="/var/lib/proxysql"

admin_variables=
{
    admin_credentials="admin:admin;radmin:radmin"
    mysql_ifaces="proxysql:6032"
}

mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    have_compress=false
    poll_timeout=2000
    interfaces="proxysql:6033"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.5.0"
    connect_timeout_server=3000
    ping_interval_server_msec=120000
    ping_timeout_server=500
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
}

mysql_servers =
(
    {
        address="mysql"
        port=3306
        hostgroup=0
        max_connections=200
    }
)

mysql_users:
(
    {
        username = "<username>" // your mysql user
        password = "<password>" // your mysql password
        default_hostgroup = 0
        max_connections=1000
        default_schema="information_schema"
        active = 1
    }
)

and you can connect with jdbc:mysql://127.0.0.1:6033/...... to MySQL

[DavideD]

Mmh... there is still something I'm missing. Following your example, I've created a test with JBang.

Once you've installed JBang, you can run it using the following commands:

1. jbang --fresh init -t issue881@DavideD/jbang-rep Issue881.java
2. docker-compose up
3. jbang Issue8881.java
4. (Optional) You can open it in your ide (idea in this case) with: jbang edit --live --open=idea Issue881.java

It will create a file Issue881.java with the test and the two additional files docker-compose.yml and proxysql.cnf but when I run it I have the following error:

Test testLocalTime(Issue881) FAILED!
        io.netty.channel.AbstractChannel$AnnotatedConnectException: Connection refused: /127.0.0.1:6603
Caused by: java.net.ConnectException: Connection refused
        at java.base/sun.nio.ch.SocketChannelImpl.checkConnect(Native Method)
        at java.base/sun.nio.ch.SocketChannelImpl.finishConnect(SocketChannelImpl.java:779)

Note that if I change the url to point to MySQL (mysql://127.0.0.1:3306/hreact), it works fine.

if you are interested to check the configuration files without installing JBang, they are here: https://github.com/DavideD/jbang-rep/tree/main/issue881

Maybe you can tell me what's missing?

[pqab]

It works if you change the port from 6603 to 6033

and change the mysql_servers.address to mysql inside the proxysql.cnf

mysql_servers =
(
    {
        address="mysql"
        port=3306
        hostgroup=0
        max_connections=200
    }
)

[DavideD]

Thanks! Those were really silly mistakes. It still doesn't work though. At start up (docker-compose upI have this error):

proxysql_1  | 2021-07-06 07:35:36 MySQL_Monitor.cpp:2620:monitor_ping(): [ERROR] Server 127.0.0.1:3306 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.

Even if the node is up and running and tests work if I use mysql directly

[pqab]

Thanks for checking. I can run with mysql:5.7.25, meanwhile I will check with mysql:8.0.25

I think you can also disable the monitor by adding the command

  proxysql:
    image: proxysql/proxysql
    restart: always
    volumes:
      - "./proxysql.cnf:/etc/proxysql.cnf"
    ports:
      - "6033:6033"
      - "6032:6032"
    command: --no-monitor

[DavideD]

Nice, now I can see the exception. It seems to only occurs with the proxy in front of the server so I suspect it's not a bug in Hibernate Reactive. I will check a bit better later.

[gavinking]

@DavideD what's the status of this? Can it be closed?

[DavideD]

Ah sorry, I need to get back to it. I will do it today

[DavideD]

So, I don't know what we can do about this, this seems more of a problem for the Vert.x SQL client team.

@tsegismont Do you have any idea what could cause this issue? It seems that ProxySQL handles the type slightly different than MySQL.

I've also tried to run the whole build with ProxySQL and there are some tests failing because the query syntax is not recognized (the failures seem to happend when locks are involved). This might work if we use a different dialect, if the problem is not in the Vert.x SQL client, or if we update the existing one. @pqab Is it something you would like to contribute? Not sure if we have the time to work on this at the moment.

[tsegismont]

@pqab can you please file a bug report in the Vert.x Reactive SQL Client project?

Hibernate Reactive relies on this client to communicate with the database. After the issue is filed, I'll try to make a reproducer without Hibernate Reactive.

[DavideD]

@tsegismont I've created the issue: https://github.com/eclipse-vertx/vertx-sql-client/issues/1016

[tsegismont]

Thank you

2021-07-29 16:21 UTC+02:00, DavideD @.***>:

@tsegismont I've created the issue: https://github.com/eclipse-vertx/vertx-sql-client/issues/1016

-- You are receiving this because you were mentioned. Reply to this email directly or view it on GitHub: https://github.com/hibernate/hibernate-reactive/issues/881#issuecomment-889187583

[vietj]

the issue has been recognised as a ProxySQL bug (the same could also happen with JDBC driver I think) : https://github.com/sysown/proxysql/issues/3585 I closed the SQL client issue as invalid

[gavinking]

OK, I'll close this one too.

Thanks, @vietj!!