Directus. Migrate from Sqlite to MySQL.

migrate.md

0. I use this docker-compose for MySQL

services:
 # Database
 db:
    image: mysql:8.0.35
    # volumes:
    #   - docker_mysql_data:/var/lib/mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: x5fond
      MYSQL_USER: x5fond
      MYSQL_PASSWORD: pass
    ports:
      - "3306:3306"
    networks:
      - mysql-phpmyadmin

 # phpMyAdmin
 phpmyadmin:
    depends_on:
      - db
    image: phpmyadmin/phpmyadmin
    restart: always
    ports:
      - "8090:80"
    environment:
      PMA_HOST: db
      MYSQL_ROOT_PASSWORD: password
    networks:
      - mysql-phpmyadmin

networks:
  mysql-phpmyadmin:

# volumes:
#   docker_mysql_data:

1. Take your Sqlite Directus and create a snapshot for it

cd sqlite-directus/
npx directus schema snapshot ./snapshot.yaml

2. Create a new Directus instance for MySQL and apply the snapshot for it.

cd mysql-directus/
vim .env # don't forget to adjust .env with MySQL parameters
npx directus bootstrap
npx directus schema apply ./snapshot.yaml

3. Take this tool https://pypi.org/project/sqlite3-to-mysql/ and install it

python3 -mvenv venv
source venv/bin/activate
pip install sqlite3-to-mysql

4. We need to disable the creation of tables by this tool. Otherwise it fails. Comment out these lines in venv/lib/python3.8/site-packages/sqlite3_to_mysql/transporter.py. Somewhere around 385 line.

        try:
            self._mysql_cur.execute(sql)
            self._mysql.commit()
        except mysql.connector.Error as err:

I made it like this

        try:
            print()
            # self._mysql_cur.execute(sql)
            # self._mysql.commit()
        except mysql.connector.Error as err:

5. Run the tool

sqlite3mysql -X -W -E --debug -f ../sqlite-directus//database/data.db -d x5fond -u root --mysql-password password