Template Install MySQL 8.4 - type / content mismatch

[philipp-winterle]

Current Behaviour

The usage of npx directus-template-cli@latest apply results in errors but partially installed collections without finished relations and missing content.

Expected Behaviour

The template is installed and no errors happen during installation

Specifications

Directus 11.4
MySQL 8.4.4
Template: agencyos / simplecms
Collation of all tables (also newly added): utf8mb4
Connection with admin api key and email/pass

Error Log

[14:39:14] POST /collections 200 47ms
[14:39:14.709] ERROR: Unexpected token '_', "_utf8mb4\'[]\'" is not valid JSON
    err: {
      "type": "SyntaxError",
      "message": "Unexpected token '_', \"_utf8mb4\\'[]\\'\" is not valid JSON",
      "stack":
tcms_nginx         | 192.168.155.1 - - [24/Jan/2025:14:39:14 +0000] "POST /collections HTTP/1.1" 200 554 "-" "node" "-"
          SyntaxError: Unexpected token '_', "_utf8mb4\'[]\'" is not valid JSON
              at JSON.parse (<anonymous>)
              at parseJSON (file:///directus/node_modules/.pnpm/@directus+utils@file+packages+utils_vue@3.5.13_typescript@5.7.3_/node_modules/@directus/utils/dist/shared/index.js:131:15)
              at castToObject (file:///directus/node_modules/.pnpm/@directus+api@file+api_@types+node@22.10.5_@unhead+vue@1.11.15_vue@3.5.13_typescript@5.7.3____rwsrw2ii2kgfeysgzcibtnsnku/node_modules/@directus/api/dist/utils/get-default-value.js:47:20)
              at getDefaultValue (file:///directus/node_modules/.pnpm/@directus+api@file+api_@types+node@22.10.5_@unhead+vue@1.11.15_vue@3.5.13_typescript@5.7.3____rwsrw2ii2kgfeysgzcibtnsnku/node_modules/@directus/api/dist/utils/get-default-value.js:21:20)
              at file:///directus/node_modules/.pnpm/@directus+api@file+api_@types+node@22.10.5_@unhead+vue@1.11.15_vue@3.5.13_typescript@5.7.3____rwsrw2ii2kgfeysgzcibtnsnku/node_modules/@directus/api/dist/utils/get-schema.js:111:35
              at file:///directus/node_modules/.pnpm/lodash-es@4.17.21/node_modules/lodash-es/mapValues.js:38:34
              at file:///directus/node_modules/.pnpm/lodash-es@4.17.21/node_modules/lodash-es/_createBaseFor.js:17:11
              at baseForOwn (file:///directus/node_modules/.pnpm/lodash-es@4.17.21/node_modules/lodash-es/_baseForOwn.js:13:20)
              at mapValues (file:///directus/node_modules/.pnpm/lodash-es@4.17.21/node_modules/lodash-es/mapValues.js:37:3)
              at getDatabaseSchema (file:///directus/node_modules/.pnpm/@directus+api@file+api_@types+node@22.10.5_@unhead+vue@1.11.15_vue@3.5.13_typescript@5.7.3____rwsrw2ii2kgfeysgzcibtnsnku/node_modules/@directus/api/dist/utils/get-schema.js:108:21)
    }
[15:02:46.293] ERROR: alter table `block_gallery` add primary key `block_gallery_pkey`(`id`) - Multiple primary key defined
    err: {
      "type": "Error",
      "message": "alter table `block_gallery` add primary key `block_gallery_pkey`(`id`) - Multiple primary key defined",
      "stack":
          Error: alter table `block_gallery` add primary key `block_gallery_pkey`(`id`) - Multiple primary key defined
              at Packet.asError (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/packets/packet.js:740:17)
              at Query.execute (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/commands/command.js:29:26)
              at Connection.handlePacket (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:475:34)
              at PacketParser.onPacket (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:93:12)
              at PacketParser.executeStart (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/packet_parser.js:75:16)
              at Socket.<anonymous> (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:100:25)
              at Socket.emit (node:events:524:28)
              at addChunk (node:internal/streams/readable:561:12)
              at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
              at Readable.push (node:internal/streams/readable:392:5)
      "code": "ER_MULTIPLE_PRI_KEY",
      "errno": 1068,
      "sqlState": "42000",
      "sqlMessage": "Multiple primary key defined",
      "sql": "alter table `block_gallery` add primary key `block_gallery_pkey`(`id`)",
      "extensions": {
        "stack":
            Error: alter table `block_gallery` add primary key `block_gallery_pkey`(`id`) - Multiple primary key defined
                at Packet.asError (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/packets/packet.js:740:17)
                at Query.execute (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/commands/command.js:29:26)
                at Connection.handlePacket (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:475:34)
                at PacketParser.onPacket (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:93:12)
                at PacketParser.executeStart (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/packet_parser.js:75:16)
                at Socket.<anonymous> (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:100:25)
                at Socket.emit (node:events:524:28)
                at addChunk (node:internal/streams/readable:561:12)
                at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
                at Readable.push (node:internal/streams/readable:392:5)
      }
    }

Additionally and maybe helpful. The fourth value inserted is the breaking one. '_utf8mb4\'{}\'' - The fourth column is command_palette_settings in this case. Maybe related

Warning: Directus Error: insert into `directus_settings` (`auth_login_attempts`, `auth_password_policy`, `basemaps`,
 ›   `command_palette_settings`, `custom_aspect_ratios`, `custom_css`, `default_appearance`, `default_language`, `default_theme_dark`,
 ›   `default_theme_light`, `id`, `mapbox_key`, `module_bar`, `project_color`, `project_descriptor`, `project_logo`, `project_name`,
 ›   `project_url`, `public_background`, `public_favicon`, `public_foreground`, `public_note`, `public_registration`,
 ›   `public_registration_email_filter`, `public_registration_role`, `public_registration_verify_email`, `report_bug_url`,
 ›   `report_error_url`, `report_feature_url`, `storage_asset_presets`, `storage_asset_transform`, `storage_default_folder`,
 ›   `theme_dark_overrides`, `theme_light_overrides`) values (25, '/^.{8,}$/', NULL, '_utf8mb4\\\'{}\\\'', NULL,
Other Tests
Error does not occur with postgresql db
MySQL 5 Other error. Same problem
Error: create table `globals` (`description` text null, `id` char(36) not null, `social_links` json null default ('[]'), `tagline` varchar(255) null, `title` varchar(255) null, `url` varchar(255) null, `favicon` char(36) null, `logo` char(36) null, `openai_api_key` varchar(255) null, `directus_url` varchar(255) null, `logo_dark_mode` char(36) null, `accent_color` varchar(255) null, primary key (`id`)) - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('[]'), `tagline` varchar(255) null, `title` varchar(255) null, `url` varchar(25' at line 1
              at Packet.asError (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/packets/packet.js:740:17)
              at Query.execute (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/commands/command.js:29:26)
              at Connection.handlePacket (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:475:34)
              at PacketParser.onPacket (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:93:12)
              at PacketParser.executeStart (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/packet_parser.js:75:16)
              at Socket.<anonymous> (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:100:25)
              at Socket.emit (node:events:524:28)
              at addChunk (node:internal/streams/readable:561:12)
              at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
              at Readable.push (node:internal/streams/readable:392:5)
      "code": "ER_PARSE_ERROR",
      "errno": 1064,
      "sqlState": "42000"
Issue with json null default ('[]')

The utf8mb4 error occurs only while installing the directus_settings command palette JSON which is not in valid format `'_utf8mb4\'{}\''.

The other issue is that the primary keys will be updated every time so mysql throws an error as they would be duplications.

Warning: Directus Error: alter table `pages` add primary key `pages_pkey`(`id`) - Multiple primary key defined (Status: 500)
 ›   {"code":"INTERNAL_SERVER_ERROR","stack":"Error: alter table `pages` add primary key `pages_pkey`(`id`) - Multiple primary ke

[philipp-winterle]

Digged something deeper. Could be MySQL related and furthermore has to be fixed in directus or mysql2

https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html#data-type-defaults.html#data-type-defaults-explicit

Seems like the default value for JSON fields in snapshot.json is the problem.

MySQL 8.0.13 onwards
The BLOB, TEXT, GEOMETRY, and JSON data types can be assigned a default value only if the value is written as an expression, even if the expression value is a literal.

Alternative

ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT ('{}');

OR

ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT (JSON_OBJECT())

[bryantgillespie]

Yup. It definitely looks to be a MySQL issue. I'm sure it's not helpful if you prefer MySQL, but we do call out that Postgres is the recommend db in the readme. And as we're just using the API in this tool, it's probably better as an upstream issue so we don't have any crazy workarounds.

Is MySQL a hard requirement for your project?

[philipp-winterle]

Unfortunately this is a business case and MySQL is the only option. Iam already checking in the directus source code if I can find the snapshot apply section to see if one can implement the JSON default stated above.
It is not a MySQL issue. More an error in querying with MySQL as the documentation is clear in how to not set JSON default values.

Thanks for fast answer so far :)

[bryantgillespie]

Well let me clarify - a MySQL issue within context of this tool 😢

[philipp-winterle]

After digging a bit deeper into the code I found that the template cli is using the @directus/sdk which is a part of directus itself. This uses only the REST API of directus itself as it is created with createDirectus(...).with(rest()).

I did not find any hint that the template cli tool provides a wrong json mapping.

What I did not find out yet is why directus is handling things different for the rest api call and internally. Because if I apply the snapshot it does not use the valid json default value setting. If I save in the admin it does save it as valid value. If I then after saving manually reapply the template via the cli it has the right value set.
This is because the value is not the same as a default value.

Result: It all depends on how the default value is set and this should be done right by the rest api

EDIT

I did a fresh install with postresql und then extracted the template with the cli. Then I removed all the default values from JSON fields (actually there were no default values but malformed). I setup a new mysql db and started over with a fresh startup, applied the fixed template with --templateType=local and everything went through.

So that proofs that this is just a json field default issue.

Test Template: simple-website-cms

BTW: You should mention in the readme how to use the community type, which is --templateLocation="PACKAGE_JSON_TEMPLATE_NAME". I was expecting the folder name.

[philipp-winterle]

After trying the new versions of starter templates I created a python script to fix the template snapshot and fields.

This prevents the cli tool to break the import on MySQL. The script contains all changes needed for MySQL.

The only error that still happens is when the fields get applied to be primary as they are already and this results in duplicate primary key error, which is not an issue atm because the fields are already primary keys because of snapshot.

FYI: Error output at the bottom of this comment.

import json
import os
import sys
import argparse

# Function to change data types
def update_schema_for_mysql(file_path, verbose=False):
    # Read file
    try:
        with open(file_path, 'r') as file:
            data = json.load(file)
    except FileNotFoundError:
        print(f"Error: File '{file_path}' not found.")
        return False
    except json.JSONDecodeError:
        print(f"Error: File '{file_path}' does not contain valid JSON.")
        return False

    # Counter for changes
    changes = {
        'uuid_to_char': 0,
        'integer_to_int': 0,
        'character_varying_to_varchar': 0,
        'json_default_null': 0,
        'timestamp_to_datetime': 0
    }

    # Perform changes
    def process_schema(obj):
        if isinstance(obj, dict):
            if 'schema' in obj and isinstance(obj['schema'], dict):
                schema = obj['schema']

                # 1. uuid to char with max_length 36
                if 'data_type' in schema and schema['data_type'] == 'uuid':
                    schema['data_type'] = 'char'
                    if 'max_length' in schema and schema['max_length'] is None:
                        schema['max_length'] = 36
                    changes['uuid_to_char'] += 1

                # 2. integer to int with numeric_precision 10
                elif 'data_type' in schema and schema['data_type'] == 'integer':
                    schema['data_type'] = 'int'
                    if 'numeric_precision' in schema and schema['numeric_precision'] is None:
                        schema['numeric_precision'] = 10
                    changes['integer_to_int'] += 1

                # 3. character varying to varchar
                elif 'data_type' in schema and schema['data_type'] == 'character varying':
                    schema['data_type'] = 'varchar'
                    changes['character_varying_to_varchar'] += 1

                # 4. json with default_value null
                elif 'data_type' in schema and schema['data_type'] == 'json':
                    if schema.get('default_value') is not None:
                        schema['default_value'] = None
                        changes['json_default_null'] += 1

                # 5. timestamp with time zone to datetime
                elif 'data_type' in schema and schema['data_type'] == 'timestamp with time zone':
                    schema['data_type'] = 'datetime'
                    changes['timestamp_to_datetime'] += 1

            for key, value in obj.items():
                if isinstance(value, (dict, list)):
                    process_schema(value)
        elif isinstance(obj, list):
            for item in obj:
                process_schema(item)

    # Process the data
    process_schema(data)

    # Write file
    try:
        with open(file_path, 'w') as file:
            json.dump(data, file, indent=2)
    except Exception as e:
        print(f"Error writing file: {str(e)}")
        return False

    # Output of changes
    if verbose:
        print(f"\nChanges in '{file_path}':")
        print(f"- UUID to CHAR: {changes['uuid_to_char']}")
        print(f"- INTEGER to INT: {changes['integer_to_int']}")
        print(f"- CHARACTER VARYING to VARCHAR: {changes['character_varying_to_varchar']}")
        print(f"- JSON default_value set to NULL: {changes['json_default_null']}")
        print(f"- TIMESTAMP WITH TIME ZONE to DATETIME: {changes['timestamp_to_datetime']}")

    total_changes = sum(changes.values())
    print(f"Total of {total_changes} changes successfully applied.")
    return True

# Main function
def main():
    parser = argparse.ArgumentParser(description='Converts PostgreSQL data types to MySQL compatible data types in JSON schema files.')
    parser.add_argument('file_path', nargs='?', default='./snapshot.json',
                        help='Path to the JSON file to be converted (default: ./snapshot.json)')
    parser.add_argument('-v', '--verbose', action='store_true',
                        help='Detailed output of changes made')

    args = parser.parse_args()

    print(f"Updating schema in: {args.file_path}")
    success = update_schema_for_mysql(args.file_path, args.verbose)

    if success:
        print("All changes were successfully applied.")
    else:
        print("Errors occurred. Please check the output.")
        sys.exit(1)

if __name__ == "__main__":
    main()

Error Primary ID

[09:34:53] PATCH /fields/pages/id 500 121ms
[09:34:57.316] ERROR: alter table `pages` add primary key `pages_pkey`(`id`) - Multiple primary key defined
    err: {
      "type": "Error",
      "message": "alter table `pages` add primary key `pages_pkey`(`id`) - Multiple primary key defined",
      "stack":
          Error: alter table `pages` add primary key `pages_pkey`(`id`) - Multiple primary key defined
              at Packet.asError (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/packets/packet.js:740:17)
              at Query.execute (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/commands/command.js:29:26)
              at Connection.handlePacket (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:475:34)
              at PacketParser.onPacket (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:93:12)
              at PacketParser.executeStart (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/packet_parser.js:75:16)
              at Socket.<anonymous> (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:100:25)
              at Socket.emit (node:events:524:28)
              at addChunk (node:internal/streams/readable:561:12)
              at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
              at Readable.push (node:internal/streams/readable:392:5)
      "code": "ER_MULTIPLE_PRI_KEY",
      "errno": 1068,
      "sqlState": "42000",
      "sqlMessage": "Multiple primary key defined",
      "sql": "alter table `pages` add primary key `pages_pkey`(`id`)",
      "extensions": {
        "stack":
            Error: alter table `pages` add primary key `pages_pkey`(`id`) - Multiple primary key defined
                at Packet.asError (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/packets/packet.js:740:17)
                at Query.execute (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/commands/command.js:29:26)
                at Connection.handlePacket (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:475:34)
                at PacketParser.onPacket (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:93:12)
                at PacketParser.executeStart (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/packet_parser.js:75:16)
                at Socket.<anonymous> (/directus/node_modules/.pnpm/mysql2@3.12.0/node_modules/mysql2/lib/base/connection.js:100:25)
                at Socket.emit (node:events:524:28)
                at addChunk (node:internal/streams/readable:561:12)
                at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
                at Readable.push (node:internal/streams/readable:392:5)
      }
    }