Migrate from AWS RDS MySQL to GCP cloud sql

[obriensystems]

https://cloud.google.com/sql/mysql

mysql dump

osx
ssh -L 3406:prod.clck32qqdksy.us-east-1.rds.amazonaws.com:3306 [email protected]

Running: /Applications/MySQLWorkbench.app/Contents/MacOS/mysqldump --defaults-file="/var/folders/rj/kcxp4sbj2y18fnl4sq1gdvj40000gn/T/tmpzieuv1uh/extraparams.cnf"  --host=127.0.0.1 --port=3406 --default-character-set=utf8 --user=ob...bs --protocol=tcp --skip-triggers --skip-column-statistics "biom" "gps_record"

[obriensystems]

see https://github.com/ObrienlabsDev/blog/wiki/Developer-Guide#mysql-on-docker

# mysql 5.7.44 legacy database version
# arm
docker run --name mysql-dev0 -v mysql-data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -d -p 3506:3306 arm64v8/mysql:8.0.38
# windows
docker run --name mysql-dev0 -v mysql-data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -d -p 3506:3306 mysql:5.7
# client container
docker run --name mysql-dev2 -it --network="host" --rm arm64v8/mysql:8.0.38 sh -c "mysql -h 127.0.0.1 -P 3506 -u root -p"
docker run --name mysql-dev2 -it --network="host" --rm mysql:5.7 sh -c "mysql -h 127.0.0.1 -P 3506 -u root -p"

# mysql workbench import

16:17:38 Restoring /Users/michaelobrien/dumps/Dump20240709_w_schema.sql
Running: /Applications/MySQLWorkbench.app/Contents/MacOS/mysql --defaults-file="/var/folders/rj/kcxp4sbj2y18fnl4sq1gdvj40000gn/T/tmp2t1ia59j/extraparams.cnf"  --protocol=tcp --host=127.0.0.1 --user=root --port=3506 --default-character-set=utf8 --comments  < "/Users/mi..en/dumps/Dump20240709_w_schema.sql"
16:22:36 Import of /Users/michaelobrien/dumps/Dump20240709_w_schema.sql has finished

mysql> select count(1) from biometric.gps_record;
+----------+
| count(1) |
+----------+
| 15456722 |
+----------+
1 row in set (1.51 sec)

15 million records

[obriensystems]

Spring Data JPA

• see Architecture Implementation #3
• 80b18e2

pom.xml

database
 docker run --name mysql-dev0 -v mysql-data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=... -d -p 3506:3306 arm64v8/mysql:8.0.38

CONTAINER ID   IMAGE                  COMMAND                  CREATED      STATUS         PORTS                               NAMES
f4b8fc9ce4d0   arm64v8/mysql:8.0.38   "docker-entrypoint.s…"   4 days ago   Up 6 minutes   33060/tcp, 0.0.0.0:3506->3306/tcp   mysql-dev0

	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-data-jpa</artifactId>
	</dependency>
        <dependency>
        	<groupId>mysql</groupId>
        	<artifactId>mysql-connector-java</artifactId>
        	<!-- version>5.1.42</version--><!-- for 5.7.44 - use 8.0.33 for 8.0.38 -->
        	<scope>runtime</scope>
    </dependency>

Repositories

@Repository
public class RecordRepositoryImpl {// implements RecordRepository {
	  @PersistenceContext
	  private EntityManager entityManager;
	  //Record findById(long id);
	  //@Override
	  @Transactional
	  public void persist(Record record) {
		  entityManager.persist(record);
	  }
}

JPA Entities

@MappedSuperclass
public class DataObject {
    @Version
    private Long version;
}

@MappedSuperclass
public class IdentifiableDataObject extends DataObject {
    @Id
    @Column(name="IDENT_ID")
    @GeneratedValue(strategy = GenerationType.IDENTITY)//.AUTO)   
    @XmlElement
    private Long id;
}

@Entity
@Table(name="gps_record")
@Access(value = AccessType.FIELD)
@XmlType
@XmlRootElement
@XmlAccessorType(XmlAccessType.FIELD)
public class Record extends IdentifiableDataObject {
    @XmlElement
    @Column(name="userId", nullable=false)
    private Long userId;
    // 20240713: https://stackoverflow.com/questions/25283198/spring-boot-jpa-column-name-annotation-ignored
    //@Column(name="ts_start")
    @XmlElement 
    private Long tsStart;
}

For the problem where the column names were auto converting from tsStart to ts_start - gemini was no help - stackoverflow as usually was.
https://stackoverflow.com/questions/25283198/spring-boot-jpa-column-name-annotation-ignored

# https://docs.spring.io/spring-boot/docs/1.1.0.M1/reference/html/howto-database-initialization.html
#spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.hibernate.ddl-auto=none
#spring.jpa.properties.hibernate.globally_quoted_identifiers=true
spring.datasource.url=jdbc:mysql://127.0.0.1:3506/biometric
spring.datasource.username=root
spring.datasource.password=...
# deprecated
#spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver

# override auto conversion of tsStart to ts_start
# see https://stackoverflow.com/questions/25283198/spring-boot-jpa-column-name-annotation-ignored
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

The use of EntityManager.persist(entity) over CrudRepository.save(entity, Long) - required @EnableJpaRepositories("com.delivery.repository") on the application class to pick up a non interface repository

@SpringBootApplication
@EnableJpaRepositories("dev.obrienlabs.biometric.repository")
public class BiometricNbiApplication {
	public static void main(String[] args) {
		SpringApplication.run(BiometricNbiApplication.class, args);
	}

for
@Repository
public class RecordRepositoryImpl {// implements RecordRepository {
	  @PersistenceContext
	  private EntityManager entityManager;
	  //Record findById(long id);
	  //@Override
	  @Transactional
	  public void persist(Record record) {
		  entityManager.persist(record);
	  }
}

Working

http://127.0.0.1:8080/nbi/swagger-ui.html#/api-controller/getGpsUsingGET

7-13 18:32:15.043 DEBUG 95337 --- [nio-8080-exec-5] o.s.web.servlet.DispatcherServlet        : GET "/nbi/api/getGps?ac=0&action=u2&al=0&arx=0&ary=0&arz=0&be=0&grx=0&gry=0&grz=0&gsx=0&gsy=0&gsz=0&hr1=0&hr2=0&hrd1=0&hrd2=0&hu=0&lax=0&lay=0&laz=0&lg=0&li=0&lt=0&mfx=0&mfy=0&mfz=0&p=0&pr=0&px=0&rvx=0&rvy=0&rvz=0&s=0&te=0&ts=0&u=202407130&up=0", parameters={masked}
2024-07-13 18:32:15.044 DEBUG 95337 --- [nio-8080-exec-5] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped to dev.obrienlabs.biometric.nbi.controller.ApiController#getGps(String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, String, HttpServletRequest)
2024-07-13 18:32:15.079 DEBUG 95337 --- [nio-8080-exec-5] m.m.a.RequestResponseBodyMethodProcessor : Using 'text/plain', given [*/*] and supported [text/plain, */*, text/plain, */*, application/json, application/*+json, application/json, application/*+json]
2024-07-13 18:32:15.079 DEBUG 95337 --- [nio-8080-exec-5] m.m.a.RequestResponseBodyMethodProcessor : Writing ["Record(id=15458116,uid=202407130,ssq=0,rsq=0,hr1=0,hr2=0,lat=0.0,lon=0.0,bea=0,alt=0.0,tst0,tsp=1720 (truncated)..."]
2024-07-13 18:32:15.081 DEBUG 95337 --- [nio-8080-exec-5] o.s.web.servlet.DispatcherServlet        : Completed 200 OK

select * from biometric.gps_record where userId="202407130"