Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Hibernate: Create Mysql InnoDB tables instead of MyISAM

People also ask

Is InnoDB better than MyISAM?

Over recent years, InnoDB has shown to perform better and be more reliable. A big reason to use InnoDB over MyISAM, is the lack of full table-level locking. This allows your queries to process faster. Are You Using MyISAM or InnoDB?

How do I make an InnoDB table?

To create an InnoDB table, specify an ENGINE = InnoDB option in the CREATE TABLE statement: CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB; The statement creates a table and an index on column a in the InnoDB tablespace that consists of the data files that you specified in my. cnf .

Why is MyISAM faster than InnoDB?

MyISAM will out-perform InnoDB on large tables that require vastly more read activity versus write activity. MyISAM's readabilities outshine InnoDB because locking the entire table is quicker than figuring out which rows are locked in the table.


Can't you specify the Hibernate dialect and use

hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect

Edit

From MySQL version > 5.1 this should be

hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

to avoid running into this issue Using "TYPE = InnoDB" in MySQL throws exception


Go to this link:

mysql-dialect-refactoring

It clearly says :

Traditionally, MySQL used the non-transactional MyISAM storage engine, and this is the default storage engine for all Dialects that are older than MySQL55Dialect. From MySQL55Dialect onwards, the InnoDB storage engine is used by default.

Put the following in your application.properties (or in your config):

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL55Dialect

Notice 55 in above. - not just 5.

And you can see it in the console too:

Hibernate: create table users_events (user_id bigint not null, event_id bigint not null) engine=InnoDB
Hibernate: create table users_roles (user_id bigint not null, role_id bigint not null) engine=InnoDB

Hope it helps.


Are you specifying the dialect setting in your hibernate configuration? If not, then Hibernate will attempt to auto-detect the database dialect, and will choose the safest MySQL dialec, which is MySQL 4 MyISAM.

You can give it a specific dialect, by adding this to your hibernate properties:

hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

With spring-boot 2.0.0M7 following did work for me (mysqld 5.7)

spring.jpa.hibernate.use-new-id-generator-mappings: true
spring.jpa.database-platform: org.hibernate.dialect.MySQL5InnoDBDialect