Unable to insert to database (mariadb) after DB version upgrade
yujietan opened this issue · 2 comments
yujietan commented
Questions
Do not use this issue tracker to ask questions, instead use one of these channels. Questions will likely be closed without notice.
Version
implementation(enforcedPlatform("io.quarkus:quarkus-universe-bom:3.1.2.Final"))
implementation("io.quarkus:quarkus-kotlin")
implementation("io.quarkus:quarkus-jackson")
implementation("io.quarkus:quarkus-hibernate-reactive-panache")
implementation("io.quarkus:quarkus-reactive-mysql-client")
implementation("io.quarkus:quarkus-hibernate-validator")
testImplementation("org.junit.jupiter:junit-jupiter-api:5.9.1")
testRuntimeOnly("org.junit.jupiter:junit-jupiter-engine:5.9.1")
testImplementation("io.quarkus:quarkus-junit5")
Context
Do you have a reproducer?
Unable to provide enterprise mariadb with maxscale to reproduce the issue
- Link to github project/gist
Steps to reproduce
Hi , we tried building a reproducer but was not able to find any mariabdb image for 10.6.12-8 Enteprise
So we created the java and kotlin class required to replicate the issue
We did it using quarkus version 3.1.2.FINAL (tried with 2.16.6.FINAL the version we are using same error)
Sorrry for not uploading this as a reproducer project. Blocked by corporate network :(
Gradle Config
build.gradle
plugins {
kotlin("jvm")
id("java-library")
id("io.quarkus")
}
allprojects {
group = "com.trial.demo"
version = "1.0.0-SNAPSHOT"
}
dependencies {
implementation(enforcedPlatform("io.quarkus:quarkus-universe-bom:3.1.2.Final"))
implementation("io.quarkus:quarkus-kotlin")
implementation("io.quarkus:quarkus-jackson")
implementation("io.quarkus:quarkus-hibernate-reactive-panache")
implementation("io.quarkus:quarkus-reactive-mysql-client")
implementation("io.quarkus:quarkus-hibernate-validator")
testImplementation("org.junit.jupiter:junit-jupiter-api:5.9.1")
testRuntimeOnly("org.junit.jupiter:junit-jupiter-engine:5.9.1")
testImplementation("io.quarkus:quarkus-junit5")
}
Schema Files
CustomTable.java
package com.trial.demo.entities;
import com.trial.demo.entities.mapper.HashMapConverter;
import jakarta.persistence.*;
import java.time.Instant;
import java.util.Map;
@Entity
@Table(name = "custom_table",
uniqueConstraints = {@UniqueConstraint(name = "UNIQUE_ACTION_MESSAGE", columnNames = {"two", "one", "ten"})})
public class CustomTable {
public CustomTable() {
}
@Id
@SequenceGenerator(
name="custom_table_mock_seq",
sequenceName = "custom_table_seq",
allocationSize = 1
)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "custom_table_mock_seq")
private Long id;
@Column(name = "one", updatable = false, nullable = false)
private int one;
@Column(name = "two", updatable = false, nullable = false)
private String two;
private String three;
private String four;
private String five;
private String six;
private Instant ten;
private Instant zero;
private int seven;
private String eight;
private String nine;
@Convert(converter = HashMapConverter.class)
private Map<String, String> eleven;
public Long getId() {
return id;
}
public int getOne() {
return one;
}
public void setOne(int one) {
this.one = one;
}
public String getThree() {
return three;
}
public void setThree(String three) {
this.three = three;
}
public String getFour() {
return four;
}
public void setFour(String four) {
this.four = four;
}
public String getFive() {
return five;
}
public void setFive(String five) {
this.five = five;
}
public String getSix() {
return six;
}
public void setSix(String six) {
this.six = six;
}
public Instant getTen() {
return ten;
}
public void setTen(Instant ten) {
this.ten = ten;
}
public Instant getZero() {
return zero;
}
public void setZero(Instant zero) {
this.zero = zero;
}
public int getSeven() {
return seven;
}
public void setSeven(int seven) {
this.seven = seven;
}
public String getEight() {
return eight;
}
public void setEight(String eight) {
this.eight = eight;
}
public String getNine() {
return nine;
}
public void setNine(String nine) {
this.nine = nine;
}
public Map<String, String> getEleven() {
return eleven;
}
public void setEleven(Map<String, String> eleven) {
this.eleven = eleven;
}
public String getTwo() {
return two;
}
public void setTwo(String two) {
this.two = two;
}
}
HashMapConverter.java
package com.trial.demo.entities.mapper;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import jakarta.enterprise.context.ApplicationScoped;
import jakarta.persistence.AttributeConverter;
import java.util.Map;
@ApplicationScoped
public class HashMapConverter implements AttributeConverter<Map<String,String>, String> {
@Override
public String convertToDatabaseColumn(Map<String, String> attribute) {
if(attribute == null){
return null;
}
String dataInDbFormat = null;
ObjectMapper om = new ObjectMapper();
try{
dataInDbFormat = om.writeValueAsString(attribute);
} catch (JsonProcessingException e) {
e.printStackTrace();
}
return dataInDbFormat;
}
@Override
public Map<String, String> convertToEntityAttribute(String dbData) {
if(dbData == null){
return null;
}
Map dataMap = null;
ObjectMapper om = new ObjectMapper();
try{
dataMap = om.readValue(dbData, Map.class);
} catch (JsonProcessingException e) {
e.printStackTrace();
}
return dataMap;
}
}
DAO Classes
CustomTableRepository.kt
package com.trial.demo
import com.trial.demo.entities.CustomTable
import io.quarkus.hibernate.reactive.panache.PanacheRepository
import io.quarkus.hibernate.reactive.panache.common.runtime.ReactiveTransactional
import io.smallrye.mutiny.Uni
import jakarta.enterprise.context.ApplicationScoped
@ApplicationScoped
class CustomTableRepository : PanacheRepository<CustomTable> {
companion object {
fun String.toPanacheSQL(): String = "#$this"
}
@ReactiveTransactional
internal fun addDataToMariaDb(data: List<CustomTable>): Uni<Void> {
return this.persist(data)
}
}
Unit Test Class For Replicating the Issue
SqlErrorTest.java
package com.trial.demo
import com.trial.demo.entities.CustomTable
import io.quarkus.hibernate.reactive.panache.Panache
import io.quarkus.hibernate.reactive.panache.Panache.flush
import io.quarkus.test.junit.QuarkusTest
import io.quarkus.vertx.VertxContextSupport
import jakarta.inject.Inject
import org.hibernate.reactive.mutiny.Mutiny.SessionFactory
import org.junit.jupiter.api.Test
import java.time.LocalDateTime
import java.time.ZoneOffset
@QuarkusTest
class SqlErrorTest {
@Inject
lateinit var sessionFactory: SessionFactory
@Inject
lateinit var customTableRepository: CustomTableRepository
fun generateTestData(): List<CustomTable> {
var firstData = CustomTable()
var secondData = CustomTable()
firstData.one = 1
firstData.two = "HAPPY_2"
firstData.three = "HAPPY_3"
firstData.four = "HAPPY_4"
firstData.five = "HAPPY_5"
firstData.six = "HAPPY_6"
firstData.seven = 0
firstData.eight = "HAPPY_8"
firstData.nine = "HAPPY_9"
firstData.ten = LocalDateTime.now().toInstant(ZoneOffset.UTC)
firstData.zero = LocalDateTime.now().toInstant(ZoneOffset.UTC)
firstData.eleven = mapOf(
Pair("1", "1"),
Pair("2", "2"),
Pair("10","{\"\$aa-aa_aa_aa\":\"123\",\"\$bb-bb_bbb\":\"1a2b3c\",\"@cc-cc-8\":\"aaa_bbb_ccc=ddd\",\"@ee-ee_ee-3\":\"a[3]=ee_ee\",\"\$dd-dd\":\"1234\",\"\$ff-ff_aa\":\"-1\",\"\$gg-gg\":\"gtg\",\"@dd-dd-10\":\"ff_ff_ff_dd=6\",\"\$zz-be\":\"344.41\",\"\$hh-hh_hh\":\"1.1.0\",\"\$tt-yy\":\"16.77\",\"@pop-pp-3\":\"ff_dd_ff=/a/b/c\",\"@aa-aa-7\":\"aa_aa_aa=aa\",\"\$aa-aa_aa\":\"/aa/aa/aa/aa/aa/aa/aa.aa\",\"\$aar-aa\":\"1\",\"@aa-aal-2\":\"aa_aa=/aa/aa_aa\",\"\$aa-aa_aa\":\"1\",\"\$aa-aa_aa\":\"11\",\"\$aa-aa\":\"0\",\"@bb-bb-6\":\"bb_bb_bb=/bb/bb/bb\",\"\$br-bb_bbt\":\"0\",\"\$bb-bu_bbt\":\"1\",\"\$br-pbt_by\":\"0\",\"@bay-abr_mb-0\":\"bb\",\"\$bb-bb\":\"bb_bb\",\"@bby-bal-11\":\"bb_bb_bb=bb\",\"@bb-bb-5\":\"bb_bb_bb=/bb/bb_bb/bb\",\"@bb-bb-4\":\"bb_bb_bb=/bb/bb/bb\",\"@array-global-1\":\"bb=bb\",\"\$bbr-bbd_bb\":\"bb\",\"\$bb-bb_bb\":\"bb-bb_bb-133abc-123\",\"@bb-bb_bb-2\":\"bb[2]=bb\",\"\$bb-bbl_bb_bb\":\"0\",\"\$bb-bb_bb\":\"bb\",\"@bb-bb-0\":\"bbL\",\"\$bb-bb_bbb\":\"0\",\"\$bb-bb\":\"bb\",\"\$bb-bb_bb\":\"0\",\"\$bb\":\"21\",\"@bb-bb_bb-1\":\"bb[1]=bb\",\"\$bb-bbb_bbb\":\"0\",\"\$bbb-bb\":\"b\",\"\$bb-bb_bb\":\"134\",\"\$gg-gge_lg\":\"0\",\"\$ggr-gt\":\"62cac\",\"@gg-gggl-9\":\"gg_gg_gg=gg\",\"\$gg-gg_gg\":\"0\",\"\$gg-egg_ggd\":\"/gg/gg/gg\"}")
)
secondData.one = 2
secondData.two = "HAPPY_2"
secondData.three = "HAPPY_3"
secondData.four = "HAPPY_4"
secondData.five = "HAPPY_5"
secondData.six = "HAPPY_6"
secondData.seven = 1
secondData.eight = "HAPPY_8"
secondData.nine = "HAPPY_9"
secondData.ten = LocalDateTime.now().toInstant(ZoneOffset.UTC)
secondData.zero = LocalDateTime.now().toInstant(ZoneOffset.UTC)
secondData.eleven = mapOf(
Pair("3", "3"),
Pair("4", "4"),
Pair("5", "!@#$%^&*()_"),
Pair("6", "=-098~\"765`432")
)
return mutableListOf(firstData, secondData)
}
/**
* Test Will Fail Due To SQL Syntax Error
*/
@Test
fun addDataWithStatelessTransaction_1() {
var listToAdd = generateTestData()
sessionFactory.withStatelessTransaction { session ->
session.insertAll(*listToAdd.toTypedArray())
}.await().indefinitely()
}
/**
* Test Will Fail Due To SQL Syntax Error
*/
@Test
fun addDataWithStatelessTransaction_2() {
var listToAdd = generateTestData()
sessionFactory.withStatelessSession { session ->
session.withTransaction { tx ->
tx.markForRollback()
session.insertAll(*listToAdd.toTypedArray())
}
}.await().indefinitely()
}
/**
* Test Will Pass Because No Transaction Is Used
*/
@Test
fun addDataWithoutTransaction() {
var listToAdd = generateTestData()
sessionFactory.withStatelessSession { session ->
session.insertAll(*listToAdd.toTypedArray())
}.await().indefinitely()
}
/**
* Test Will Fail Due To SQL Syntax Error
*/
@Test
fun addDataUsingPanache(){
var listToAdd = generateTestData()
VertxContextSupport.subscribeAndAwait {
Panache.withTransaction {
customTableRepository.persist(listToAdd)
}
}
}
/**
* Test Will Pass Because No Transaction Is Used
*/
@Test
fun addDataUsingPanacheWithoutTransaction(){
var listToAdd = generateTestData()
listToAdd[0].two = "SAD"
listToAdd[1].two = "SAD"
VertxContextSupport.subscribeAndAwait {
Panache.withSession {
customTableRepository.persist(listToAdd).flatMap { flush() }
}
}
}
}
DB Table DDL
CREATE SEQUENCE custom_table_seq START WITH 1 INCREMENT BY 1 NOCYCLE;
CREATE TABLE `custom_table` (
`id` bigint(20) NOT NULL DEFAULT nextval(`custom_table_seq`),
`one` int(11) unsigned NOT NULL,
`two` varchar(50) NOT NULL,
`three` varchar(100) DEFAULT NULL,
`four` varchar(100) DEFAULT NULL,
`five` text DEFAULT NULL,
`six` varchar(100) DEFAULT NULL,
`ten` datetime DEFAULT NULL,
`zero` datetime DEFAULT NULL,
`seven` int(11) DEFAULT NULL,
`eight` varchar(30) DEFAULT NULL,
`nine` varchar(30) DEFAULT NULL,
`eleven` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `UNIQUE_ACTION_MESSAGE` (`one`,`two`,`ten`),
KEY `idx_env` (`one`) USING BTREE,
KEY `idx_startTime_desc` (`ten`) USING BTREE
)
Extra
- Anything that can be relevant such as OS version, JVM version
yujietan commented
Hi closing issue. Turns out the db server config is different. The one where is not working has ANSI_QUOTES enabled.. Thus failing with syntax error when inserting
tsegismont commented
Thank you for letting us know