How to migrate data to new server? (Sequence generator issue)

Question

• What is the right way to backup and restore a MariaDB database that has sequence generation enabled (i.e. NOT autoincrement)? (This includes migrating to a new server.)

• Is it possible to instruct the sequence generator to pick up indexing table data at a specific ID value? How?

Steps I take to create my issue

I wish to transfer an application to a new server:

1. Backup data on source server:

mysqldump --skip-opt --no-create-db --no-create-info --hex-blob [database-name] [...list of tables...] > data-backup.sql

2. On target server, create new empty database (same name)

3. Build/run JHipster Spring application on target server: java -jar myapp.jar (Running this application recreates/configures a new instance of the database on the target server.)

4. Restore data:

mysql [database-name] < data-backup.sql

All the above steps produce no errors (so far).

Problem

When I follow these steps, the database is restored (apparently perfectly). I can log in to the application and access all information. BUT when I attempt to create new entities (i.e. save something to the database), I get an ID 'Duplicate entry' error in the server logs:

2022-03-24 12:54:43.775 ERROR 11277 --- [  XNIO-1 task-1] o.h.e.jdbc.batch.internal.BatchingBatch  : HHH000315: Exception executing batch [java.sql.BatchUpdateException: (conn=33) Duplicate entry '1001' for key 'PRIMARY'], SQL: insert into product (name, id) values (?, ?)
2022-03-24 12:54:43.776  WARN 11277 --- [  XNIO-1 task-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1062, SQLState: 23000
2022-03-24 12:54:43.776 ERROR 11277 --- [  XNIO-1 task-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : (conn=33) Duplicate entry '1001' for key 'PRIMARY'
2022-03-24 12:54:43.779 ERROR 11277 --- [  XNIO-1 task-1] o.z.problem.spring.common.AdviceTraits   : Internal Server Error

org.springframework.dao.DataIntegrityViolationException: could not execute batch; SQL [insert into product (name, id) values (?, ?)]; constraint [PRIMARY]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:276)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
        at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:566)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:654)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:407)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:698)
        at com.mycompany.app.web.rest.ProductResource$$EnhancerBySpringCGLIB$$84c14d6d.createProduct(<generated>)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
...

Clearly my backup/restore process is not accounting properly for the sequence generator, which generates ID values that conflict with the existing data.

What I am doing wrong? What is the right process of backing up/restoring such a database?

---

Environment: JHipster 7.7.0 (Angular, monolithic), MariaDB 10.4, OpenJDK 16.0.2_7, OS Windows 10 Pro and openSUSE 15.2, Firefox 98.0.2 and Chrome 99.0.4844.84.

PS: I previously reported this issue here, aimed at the JHipster community, but got limited response. I think I need a MySQL/MariaDB expert opinion on this.

(Apologies in advance: I'm not a database expert. The technique I outline above has served me well for years, but previously I was dealing with AUTO_INCREMENT. This sequence generator has me baffled.)

Answer 1

Ok! I have solutions.

[For the sake if these notes, let's call the database: mydata. Also, in JHipster, the MariaDB sequence generator is called: sequence_generator]

Let's consider two situations:

(1) Simple migration

If you are merely migrating the application to a new server, the process is straight forward:

Step 1: On the original server backup and secure your database: mysqldump -u root -p mydata > mydata.sql

Step 2: Transfer the SQL file to the new server, along with the JHipster JAR file

Step 3: On the new server, create an empty database with the same name, and restore the data: mysql -u root -p mydata < mydata.sql

Step 4: Now launch your JHipster application, and everything should work

(2) Model modification

The assumption is that you have modified your model in some way (e.g. added properties to one or more entities). This solution is fiddly, but it works (for me).

Step 1: Backup your database, and secure it (in case something goes wrong): mysqldump -u root -p mydata > mydata.sql

Step 2: Backup and secure the original JHipster JAR that works with the original database

Step 3: Duplicate your database (schema and data) in a new table: mydata_bk

Step 4: Drop your original database, and create a new empty database

Step 5: Launch your new JHipster JAR, and give it time to create the new database schema, then stop the application

Step 6: Use a tool (DataGrip, sqlYog, etc) to compare the old (mydata_bk) and new schema (mydata), and modify the old schema to match the new schema

Step 7: Restore/copy all data from mydata_bk to mydata, EXCEPT for the tables DATABASECHANGELOG, DATABASECHANGELOGLOCK and the special sequence_generator table

Step 8: Open the mydata.sql SQL file, and at the top, after initial comments, one of the first instructions will read:

--
-- Sequence structure for `sequence_generator`
--

DROP SEQUENCE IF EXISTS `sequence_generator`;
CREATE SEQUENCE `sequence_generator` start with 2000 minvalue 1 maxvalue 9223372036854775806 increment by 50 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`sequence_generator`, 201050, 0);

The specific numbers may vary, but the broad details will be similar. In a MariaDB SQL console type/execute each of those SQL statements: DROP SEQUENCE ...;, CREATE SEQUENCE ...;, and SELECT SETVAL(...);

Step 9: Launch your JHipster application.

Hope this helps others that run into similar issues. Let me know if you have a better approach!