Import data from existing Providers

This guide explains how to get the existing data from an isolated microservice into the new Social Media Service.

Schema conforming

The first step before importing the data is to conform the schema of all the different microservices. New migration scripts must be created in the isolated microservices to conform the database schema to the one in the Social Media Service.

During the conforming process, set the provider column of all the tables having a provider column to the name of the provider of the isolated microservice. For example, if the isolated microservice is for Instagram, then the provider column should be set to INSTAGRAM. Also make sure the provider is known by the Social Media Service by checking the SupportedProvider enum in the api module.

Create a dump of the isolated microservice database

The next step is to create a database dump after running the migration scripts, using the mysqldump tool. For example, for the LinkedIn migration on intern-demo with port forwarding of the database on port 3307:

mysqldump -u root -p -h 127.0.0.1 --port=3307 --no-create-info --complete-insert \
  --ignore-table=stories_intern_demo_linkedin.DATABASECHANGELOGLOCK \
  --ignore-table=stories_intern_demo_linkedin.DATABASECHANGELOG \
  stories_intern_demo_linkedin > stories_intern_demo_linkedin_backup-selected.sql

It is important to include --no-create-info. Otherwise CREATE TABLE and DROP TABLE statements are added to the dump, which cause issues later on.

--complete-insert is also important, because the order of columns in the source and target tables is not identical. For post in LinkedIn, not even the column count is identical.

If the output contains the database name in the INSERT statements, it must be removed. For example, remove the instagram. prefix below:

-- remove the `instagram` prefix before `account`
insert into instagram.account (id, state, status, access_token, external_account_id, created_at, updated_at, token_expiration_date, provider, tenant_id)
values  (1, null, 'ERROR', '.....', null, '2024-01-18 08:08:52', '2024-01-18 08:09:51', '2024-03-17 18:15:42', 'INSTAGRAM', 'urn:newsmind:tenant:convit'),
        (2, null, 'ACTIVE', '.....', '205962655264787', '2024-01-18 08:11:14', '2024-01-18 08:11:59', '2024-03-17 18:15:42', 'INSTAGRAM', 'urn:newsmind:tenant:convit');

Not all tables need to be dumped. The isolated microservices typically have the following tables:

This is already taken care of in the mysqldump command above.

Import data

The next step is to import the data into the new Social Media Service database, either using a DB tool or the mysql command line tool.

Start by running the Social Media Service at least once so that empty tables are automatically created, then stop the service again. If the service has been running before, make sure the tables DATABASECHANGELOG and DATABASECHANGELOGLOCK are empty.

Example for the import on intern-dev for the LinkedIn migration:

mysql -u root -p -h 127.0.0.1 --port=3307 stories_intern_demo_socialmedia < stories_intern_demo_linkedin_backup-selected.sql

The Social Media Service database has the following tables: account, DATABASECHANGELOG, DATABASECHANGELOGLOCK, external_media_id_collection, hibernate_sequence, post and profile.

The data must be imported in the following order, or foreign key checks must be disabled before and re-enabled afterwards. Disabling foreign key checks is included if mysqldump was used as stated above.

1. Import account
2. Import profile
3. Import post
4. Import external_media_id_collection

Check list for the migration of existing data

Migration steps:

• Conform the isolated database schema to the one of the Social Media Service
• Export the existing data from the isolated microservice
• Import the data into the new Social Media Service