Disabling mysql sql_mode on Hibernate

Question

I have a java hibernate mysql project, one of the query keep failing to INSERT because I don't provide a value of the primary key AUTO_INCREMENT.

I traced the Hibernate query, what Hibernate really send to MySql

SET GLOBAL general_log = 'ON';
SHOW GLOBAL VARIABLES LIKE 'general_log%';

and I tail the general_log file, there seems to be some session commands sent out such as SET sql_mode='STRICT_TRANS_TABLES', which some people consider we should not do. We should keep sql_mode as empty.

2019-03-22T15:16:11.207625Z   418 Query SET sql_mode='STRICT_TRANS_TABLES'
2019-03-22T15:16:11.207987Z   418 Query SHOW WARNINGS
2019-03-22T15:16:11.213624Z   418 Query SELECT @@session.transaction_isolation
2019-03-22T15:16:11.214109Z   418 Query SELECT @@session.transaction_read_only
2019-03-22T15:16:11.221148Z   418 Query SET autocommit=0
2019-03-22T15:16:11.252061Z   418 Query rollback
2019-03-22T15:16:11.252457Z   418 Query SET autocommit=1
2019-03-22T15:16:11.514489Z   418 Query SET autocommit=0
2019-03-22T15:16:11.545532Z   418 Query DELETE FROM `db1`.`table_a`
2019-03-22T15:16:11.549415Z   418 Query commit
2019-03-22T15:16:11.550861Z   418 Query SET autocommit=1
2019-03-22T15:16:11.551402Z   418 Query SET autocommit=0
2019-03-22T15:16:11.551896Z   418 Query ALTER TABLE `db1`.`table_a` AUTO_INCREMENT = 1
2019-03-22T15:16:11.567116Z   418 Query commit
2019-03-22T15:16:11.567413Z   418 Query SET autocommit=1
2019-03-22T15:16:11.568958Z   418 Query SET autocommit=0
2019-03-22T15:16:11.569947Z   418 Query INSERT INTO `db1`.`table_a` (key_reference, customer_obj_id, is_deleted) VALUES ('1ab0ccd3-3195-45eb-a65e-8ce8cf7b9808', 1, 0)

I have confirmed that in my my.cnf

[mysqld]
sql-mode=""

Using MySQL Workbench connecting to server:

SHOW VARIABLES LIKE 'sql_mode';
Variable_name sql_mode, Value=

So I have confirmed it's not from my server config. How do I stop hibernate from spewing SET sql_mode='STRICT_TRANS_TABLES'?

Did you tried: https://stackoverflow.com/questions/14319679/how-to-set-sql-mode-hibernate — abc, Mar 22 '19 at 15:51
This: `which some people consider we should not do.` also is quite controversial. Without strict mode your database will generate warnings instead of errors (like when you put too wide type and so on) and truncate data to meet requirements. — abc, Mar 22 '19 at 16:07

score 0 · Answer 1 · answered Mar 22 '19 at 16:01

I don't think your INSERT issue has to do with sql_mode.

Even when Hibernate sets sql_mode to STRICT_TRANS_TABLES which means what?

If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement.

More Detail here

Coming back to your INSERT issue

If you are using Hibernate then you must be having some Entity say Student something like this

@Entity
public class Student {

    @Id
    @GeneratedValue (strategy = GenerationType.IDENTITY)
    private long studentId;

    // ...
}

Now when you call em.persist(student); or another way of adding Data

You don't need to provide studentId value. Hibernate will take care of that

If this also doesn't help

Pls, add Entity detail and how you are saving record, in DB part of Code.

score 0 · Accepted Answer · answered Mar 27 '19 at 14:04

I tried using the @GeneratedValue (strategy = GenerationType.IDENTITY) but for some reason it still doesn't work. Maybe it's something to do with the setup of the legacy project that I'm working on, or maybe it would require a newer hibernate. The below code works, longer than what I would expected.

@Entity
@Table(name = "table_a")
@GenericGenerator(name = "MyProject_Sequence_Generator", strategy = "org.hibernate.id.enhanced.TableGenerator", parameters = {
        @Parameter(name = TableGenerator.TABLE_PARAM, value = "table_primary_key_lookup"),
        @Parameter(name = TableGenerator.VALUE_COLUMN_PARAM, value = "next_obj_id"),
        @Parameter(name = TableGenerator.SEGMENT_COLUMN_PARAM, value = "table_name"),
        @Parameter(name = TableGenerator.OPT_PARAM, value = OptimizerFactory.POOL),
        @Parameter(name = TableGenerator.INITIAL_PARAM, value = "1"),
        @Parameter(name = TableGenerator.INCREMENT_PARAM, value = "1"),
        @Parameter(name = TableGenerator.CONFIG_PREFER_SEGMENT_PER_ENTITY, value = "true") })
public class TableAModel {
}

Disabling mysql sql_mode on Hibernate

2 Answers2