MySQL Resource Table reference Column Length Limitation Causes Error with OSS Object Storage

[best]

Describe the bug

When using MySQL database along with OSS (Object Storage Service) for file uploads, an error occurs due to the design of the MySQL data table. The reference column in the resource table is defined as VARCHAR(256), which is insufficient to store the signed URLs generated by OSS, often exceeding this length limit. This results in the following error when attempting to upload files:

failed to create resource: Error 1406 (22001): Data too long for column 'reference' at row 1

The issue does not occur in PostgreSQL or SQLite databases, as their reference columns are defined as TEXT, which accommodates longer strings.

Steps to reproduce

1. Start the memos service with a MySQL backend.
2. Register an account and configure OSS object storage as the storage backend after logging in.
3. Attempt to upload a file that uses OSS for object storage.
4. Observe the error message indicating that the data is too long for the reference column.

The version of Memos you're using

v0.22.2

Screenshots or additional context

Error Message: failed to create resource: Error 1406 (22001): Data too long for column 'reference' at row 1 Database: MySQL Object Storage: OSS (Object Storage Service)

This issue does not occur in PostgreSQL or SQLite due to their reference columns being defined as TEXT:

PostgreSQL Table Structure:

CREATE TABLE resource (
  id SERIAL PRIMARY KEY,
  uid TEXT NOT NULL UNIQUE,
  creator_id INTEGER NOT NULL,
  created_ts BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW()),
  updated_ts BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW()),
  filename TEXT NOT NULL,
  blob BYTEA,
  type TEXT NOT NULL DEFAULT '',
  size INTEGER NOT NULL DEFAULT 0,
  memo_id INTEGER DEFAULT NULL,
  storage_type TEXT NOT NULL DEFAULT '',
  reference TEXT NOT NULL DEFAULT '',
  payload TEXT NOT NULL DEFAULT '{}'
);

SQLite Table Structure:

CREATE TABLE resource (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  uid TEXT NOT NULL UNIQUE,
  creator_id INTEGER NOT NULL,
  created_ts BIGINT NOT NULL DEFAULT (strftime('%s', 'now')),
  updated_ts BIGINT NOT NULL DEFAULT (strftime('%s', 'now')),
  filename TEXT NOT NULL DEFAULT '',
  blob BLOB DEFAULT NULL,
  type TEXT NOT NULL DEFAULT '',
  size INTEGER NOT NULL DEFAULT 0,
  memo_id INTEGER,
  storage_type TEXT NOT NULL DEFAULT '',
  reference TEXT NOT NULL DEFAULT '',
  payload TEXT NOT NULL DEFAULT '{}'
);

Current MySQL Table Structure:

CREATE TABLE `resource` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(256) NOT NULL UNIQUE,
  `creator_id` INT NOT NULL,
  `created_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `filename` TEXT NOT NULL,
  `blob` MEDIUMBLOB,
  `type` VARCHAR(256) NOT NULL DEFAULT '',
  `size` INT NOT NULL DEFAULT '0',
  `memo_id` INT DEFAULT NULL,
  `storage_type` VARCHAR(256) NOT NULL DEFAULT '',
  `reference` VARCHAR(256) NOT NULL DEFAULT '',
  `payload` TEXT NOT NULL
);

Please consider updating the MySQL table structure to use TEXT for the reference column to resolve this issue.