1Panel Server Docker and new-api MySQL Migration Full Record

This migration mainly accomplished two things: first fixing the Docker port mapping failure on Debian 12, then migrating new-api from SQLite to MySQL. There were quite a few pitfalls along the way, especially iptables/nftables compatibility and SQLite to MySQL syntax differences.

Background

new-api was originally running in Docker, using an SQLite database by default. As logs, users, and channel data grew, the SQLite database size approached 200MB, making it unsuitable for long-term maintenance in a container data volume.

My goals were:

• Start a MySQL container on the 1Panel-managed server
• Fully import new-api's SQLite data into MySQL
• Test with the new container first, then switch reverse proxy traffic
• Minimize impact on production services

The most important principle in production migration is: don't directly stop the old service. Bring up the new environment first, confirm everything is working, then switch traffic.

iptables and Docker Port Mapping Issue

Error Symptom

When installing a MySQL container via 1Panel, the container can be created successfully, but fails during startup with an error like:

iptables: No chain/target/match by that name.

Looking further at Docker-related logs, you can see the nftables backend incompatibility:

iptables v1.8.9 (nf_tables): chain `DOCKER' in table `filter' is incompatible, use 'nft' tool.

Root Cause

Debian 12 bookworm defaults to iptables-nft, which is the nftables backend. Docker maintains its own DOCKER chain in iptables for port mapping, but in this environment, the chain state in the filter table is incompatible with the nft backend.

There's a subtle trap here: existing containers' port mappings may still work normally because the NAT table isn't immediately affected. What actually fails is new containers or newly added port mappings.

Fix

First back up the current iptables rules:

mkdir -p /root/backup-iptables-$(date +%Y%m%d)
iptables-save > /root/backup-iptables-$(date +%Y%m%d)/iptables-all.txt

Then switch iptables and ip6tables to legacy mode:

update-alternatives --set iptables /usr/sbin/iptables-legacy
update-alternatives --set ip6tables /usr/sbin/ip6tables-legacy

Finally restart Docker:

systemctl restart docker

If containers have restart: always or unless-stopped configured, they will automatically recover after Docker restarts. Containers using host network mode, such as openresty, are generally unaffected by this port mapping issue.

SQLite to MySQL Migration Challenges

new-api uses GORM, which can auto-create tables when first connecting to MySQL, but this doesn't mean it will auto-migrate SQLite data. The actual migration requires converting the SQLite dump into MySQL-importable SQL.

Direct conversion encounters several types of problems:

1. SQLite allows TEXT fields in primary keys or indexes; MySQL throws ERROR 1170.
2. SQLite supports partial indexes, e.g., WHERE deleted_at IS NULL, which MySQL can't directly replicate.
3. SQLite BLOB byte strings may be written as X'...', which MySQL JSON columns can't directly accept.
4. MySQL doesn't allow TEXT or BLOB fields to have default values.
5. SQLite is case-sensitive by default; MySQL's default collation is usually case-insensitive, which may cause unique key conflicts.

So this step can't rely solely on simple search-and-replace; a conversion script is more suitable for handling the structure and data.

Conversion Script Strategy

I ultimately used a script to generate MySQL-importable SQL, performing these transformations:

• TEXT fields involved in indexes, primary keys, or unique keys changed to VARCHAR(191)
• Non-indexed large text fields changed to LONGTEXT
• SQLite partial indexes converted to MySQL-compatible expression indexes
• BLOB JSON byte strings converted to UTF-8 JSON strings
• SQLite timestamps with timezone converted to MySQL DATETIME(6) literals
• All using utf8mb4_bin collation to preserve SQLite's case-sensitive behavior as much as possible

Don't write actual database passwords, root passwords, or DSNs in plaintext in public articles or repositories. Sensitive fields in the commands below are replaced with placeholders.

Blue-Green Deployment Flow

The overall process is: import data first, start the new container for testing, then switch the reverse proxy.

1. Import MySQL Data

First create the target database and user, and grant permissions:

CREATE DATABASE `one-api` CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
CREATE USER 'one-api'@'%' IDENTIFIED BY '<mysql-user-password>';
GRANT ALL PRIVILEGES ON `one-api`.* TO 'one-api'@'%';
FLUSH PRIVILEGES;

Then import the converted SQL:

mysql -u one-api -p one-api < /root/one-api-mysql-importable.sql

If you've already had new-api connect to an empty MySQL, GORM may have inserted default data. In that case, you need to clear the default data first to avoid primary key or unique key conflicts during import.

2. Start MySQL Version new-api Container

The new container listens on 3001 first, while the old container continues on 3000:

docker run -d --name new-api-mysql \
  --network 1panel-network \
  --restart unless-stopped \
  -p 3001:3000 \
  -e SQL_DSN="one-api:<mysql-user-password>@tcp(<mysql-host>:3306)/one-api?charset=utf8mb4&parseTime=True&loc=Local" \
  -e TZ=Asia/Shanghai \
  -e ERROR_LOG_ENABLED=true \
  -e BATCH_UPDATE_ENABLED=true \
  -v /home/ubuntu/data/new-api-mysql:/app/data \
  calciumion/new-api:latest --log-dir /app/logs

Two things to note here:

• The new-api container must join a Docker network that can access MySQL
• SQL_DSN should include charset=utf8mb4&parseTime=True&loc=Local

3. Test the New Container

First confirm the service status via API:

curl http://localhost:3001/api/status

It should return success: true under normal circumstances. Then check whether the data volume of core tables (users, channels, configurations, logs) matches the SQLite version.

The data volume after my migration was approximately:

Table	Row Count
abilities	10,143
channels	61
logs	434,906
users	7,146
tokens	253
options	60
vendors	12
midjourneys	137
quota_data	4,033
top_ups	304
redemptions	9
setups	1

4. Switch Reverse Proxy

After confirming the new container works correctly, modify the openresty/nginx reverse proxy to switch the upstream from the old container port to the new container port:

proxy_pass http://127.0.0.1:3001;

Then reload:

nginx -s reload

After switching, observe whether login, channel calls, log writing, and quota deduction are working normally.

5. Decommission Old Container

After confirming everything is working, stop the old SQLite version container:

docker stop new-api
docker rm new-api

Don't delete the old SQLite data volume immediately — keep it for at least a while for rollback or reconciliation purposes.

Key Checkpoints

After migration is complete, I recommend at least checking the following:

• Whether the MySQL user only has permissions on the target database
• Whether the new-api container joined the correct Docker network
• Whether SQL_DSN uses the correct database name, character set, and timezone parameters
• Whether user count, channel count, token count, and configuration items match pre-migration values
• Whether the log table can continue writing
• Whether both the frontend and admin panel are accessible after reverse proxy switching
• Whether the old SQLite database has been backed up

Summary

The biggest pitfall in this migration wasn't new-api itself, but Docker networking, iptables backend, and database dialect differences stacking together.

The more stable approach is:

1. Fix the Docker port mapping environment first
2. Write a script to convert the SQLite dump into MySQL-importable SQL
3. Test the new container with MySQL separately
4. Switch traffic via reverse proxy
5. Keep the old database and old container for a period of time

This way, even if the new environment has issues, you can quickly switch traffic back to the old container, avoiding a single migration taking down production services.