How can I migrate sqlite to mysql？

[player2019]

Describe the bug

1. I found issu https://github.com/usememos/memos/issues/3006，it said that copydb flag was removed。

2. Then, I tried last reply of https://github.com/orgs/usememos/discussions/3108, and got

root@3f02ba15de52:/var/opt/memos/sqlitebak# mysql -u memos -p memos < memos_replace.sql
Enter password:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the                    right syntax to use near 'PRAGMA foreign_keys=OFF' at line 1

3. Then I found this page, tried python from chatgpt, and got

root@0189a85bc9be:/usr/src/myapp# python py.py
MySQL tables created successfully
Traceback (most recent call last):
  File "/usr/local/lib/python3.13/site-packages/mysql/connector/connection_cext.py", line 755, in cmd_query
    self._cmysql.query(
    ~~~~~~~~~~~~~~~~~~^
        query,
        ^^^^^^
    ...<3 lines>...
        query_attrs=self.query_attrs,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
_mysql_connector.MySQLInterfaceError: Incorrect datetime value: '1695170801' for column `memos`.`migration_history`.`created_ts` at row 1

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/myapp/py.py", line 62, in <module>
    main()
    ~~~~^^
  File "/usr/src/myapp/py.py", line 54, in main
    migrate_data(sqlite_cursor, mysql_cursor)
    ~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/myapp/py.py", line 32, in migrate_data
    mysql_cursor.executemany("INSERT INTO migration_history (version, created_ts) VALUES (%s, %s)", migration_history_data)
    ~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/mysql/connector/cursor_cext.py", line 469, in executemany
    return self.execute(cast(str, stmt))
           ~~~~~~~~~~~~^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/mysql/connector/cursor_cext.py", line 351, in execute
    self._connection.cmd_query(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~^
        self._stmt_partition["mappable_stmt"],
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    ...<2 lines>...
        raw_as_string=self._raw_as_string,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/usr/local/lib/python3.13/site-packages/mysql/connector/opentelemetry/context_propagation.py", line 97, in wrapper
    return method(cnx, *args, **kwargs)
  File "/usr/local/lib/python3.13/site-packages/mysql/connector/connection_cext.py", line 763, in cmd_query
    raise get_mysql_exception(
        err.errno, msg=err.msg, sqlstate=err.sqlstate
    ) from err
mysql.connector.errors.DataError: 1292 (22007): Incorrect datetime value: '1695170801' for column `memos`.`migration_history`.`created_ts` at row 1

I need some help to migrate sqlite to mariadb.
Thank all of you！

Steps to reproduce

Describe like above.

The version of Memos you're using

0.24.0（ghcr.io/usememos/memos:latest）

Screenshots or additional context

No response

[johnnyjoygh]

Try to use AI to generate migration scripts.

[player2019]

Try to use AI to generate migration scripts.

erros of step 3 is the result of sample scripts from memos doc.

I tried give back my error to chatgpt, and got more errors. I spent several hours on chatgpt, and don't think I will get right anwser from it. So, I try to seek some help here, please move this issue to discussions, maybe more people like me also need a solution .

I found there is no clear and reliable manual to migrate SQLite to mariadb, if someone can help me to solve this error，I'm willing to write a doc to help others

[PaienNate]

Try to use AI to generate migration scripts.

erros of step 3 is the result of sample scripts from memos doc.

I tried give back my error to chatgpt, and got more errors. I spent several hours on chatgpt, and don't think I will get right anwser from it. So, I try to seek some help here, please move this issue to discussions, maybe more people like me also need a solution .

I found there is no clear and reliable manual to migrate SQLite to mariadb, if someone can help me to solve this error，I'm willing to write a doc to help others

hey. I have never migrated SQLite content to MariaDB in this project. However, I once used DataX for another project's migration from SQLite to PostgreSQL or MariaDB. DataX is no longer very active, so you can use Addax instead (they should be quite similar) https://github.com/wgzhao/Addax.

If you want to find the code I used for migrating another project with DataX, you can go here: https://github.com/PaienNate/sealdice_db_magic

[PaienNate]

Try to use AI to generate migration scripts.

erros of step 3 is the result of sample scripts from memos doc.
I tried give back my error to chatgpt, and got more errors. I spent several hours on chatgpt, and don't think I will get right anwser from it. So, I try to seek some help here, please move this issue to discussions, maybe more people like me also need a solution .
I found there is no clear and reliable manual to migrate SQLite to mariadb, if someone can help me to solve this error，I'm willing to write a doc to help others

hey. I have never migrated SQLite content to MariaDB in this project. However, I once used DataX for another project's migration from SQLite to PostgreSQL or MariaDB. DataX is no longer very active, so you can use Addax instead (they should be quite similar) https://github.com/wgzhao/Addax.

If you want to find the code I used for migrating another project with DataX, you can go here: https://github.com/PaienNate/sealdice_db_magic

@player2019