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

thank you so much for this guide!

I did it like that, but when i start my directus (mysql version) i got this error:

WARN: Some tables and columns do not match your database's default collation (utf8mb4_general_ci):
- Table "directus_flows": "utf8mb4_general_ci"
- Column "options": "utf8mb4_bin"
- Table "directus_users": "utf8mb4_general_ci"
- Column "tags": "utf8mb4_bin"
- Column "auth_data": "utf8mb4_bin"
- Column "theme_light_overrides": "utf8mb4_bin"
- Column "theme_dark_overrides": "utf8mb4_bin"
- Table "directus_panels": "utf8mb4_general_ci"
- Column "options": "utf8mb4_bin"
- Table "directus_revisions": "utf8mb4_general_ci"
- Column "data": "utf8mb4_bin"
- Column "delta": "utf8mb4_bin"
- Table "directus_fields": "utf8mb4_general_ci"
- Column "options": "utf8mb4_bin"
- Column "display_options": "utf8mb4_bin"
- Column "translations": "utf8mb4_bin"
- Column "conditions": "utf8mb4_bin"
- Column "validation": "utf8mb4_bin"
- Table "directus_operations": "utf8mb4_general_ci"
- Column "options": "utf8mb4_bin"
- Table "directus_permissions": "utf8mb4_general_ci"
- Column "permissions": "utf8mb4_bin"
- Column "validation": "utf8mb4_bin"
- Column "presets": "utf8mb4_bin"
- Table "directus_webhooks": "utf8mb4_general_ci"
- Column "headers": "utf8mb4_bin"
- Table "directus_settings": "utf8mb4_general_ci"
- Column "storage_asset_presets": "utf8mb4_bin"
- Column "basemaps": "utf8mb4_bin"
- Column "module_bar": "utf8mb4_bin"
- Column "custom_aspect_ratios": "utf8mb4_bin"
- Column "theme_light_overrides": "utf8mb4_bin"
- Column "theme_dark_overrides": "utf8mb4_bin"
- Column "public_registration_email_filter": "utf8mb4_bin"
- Column "flow_manager_categories": "utf8mb4_bin"
- Table "directus_presets": "utf8mb4_general_ci"
- Column "layout_query": "utf8mb4_bin"
- Column "layout_options": "utf8mb4_bin"
- Column "filter": "utf8mb4_bin"
- Table "google_my_business_settings": "utf8mb4_general_ci"
- Column "scopes": "utf8mb4_bin"
- Table "directus_collections": "utf8mb4_general_ci"
- Column "translations": "utf8mb4_bin"
- Column "item_duplication_fields": "utf8mb4_bin"
- Table "directus_files": "utf8mb4_general_ci"
- Column "metadata": "utf8mb4_bin"
- Column "tus_data": "utf8mb4_bin"

[14:39:53.828] INFO: Loaded extensions: ...cutted...
RangeError: Invalid time value
at Date.toISOString ()
at PayloadService.processDates (file:///Users/xxx/node_modules/@directus/api/dist/services/payload.js:250:86)
at PayloadService.processValues (file:///Users/xxx/node_modules/@directus/api/dist/services/payload.js:155:14)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async run (file:///User/sxxx/node_modules/@directus/api/dist/database/run-ast/run-
...

finally directus can't start.

So maybe its easy to fix by giving sqlite3mysql some options for the charset?

You need to set the same collation. I would try to change utf8mb4_bin to utf8mb4_general_ci. Check that your snapshot has the correct collation. Looks like it has utf8mb4_bin