It displayed problem when I attempted to migrate data from SQLite to MySQL.

[krupesh1958]

Mysql Version:- 8.0

Error:

mysql.connector.errors.DatabaseError: 3780 (HY000): Referencing column 'user_activity_id' and referenced column 'id' in foreign key constraint 'common_app_notification_FK_0_0' are incompatible.

[techouse]

Hi,

I'll need a bit more info to help you out here.

You can start by supplying the DDL and some sample data. Additionally you could also try to use the tool without transferring any foreign keys.

[krupesh1958]

2024-03-26 16:58:30 ERROR    MySQL failed creating table activity: 3780 (HY000): Referencing column 'user_activity_id' and referenced column 'id' in foreign key constraint 'common_app_notification_FK_0_0' are incompatible.
Traceback (most recent call last):
  File "/.venv/bin/sqlite3mysql", line 8, in <module>
    sys.exit(cli())
             ^^^^^
  File "/.venv/lib/python3.11/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/.venv/lib/python3.11/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/.venv/lib/python3.11/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/.venv/lib/python3.11/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/.venv/lib/python3.11/site-packages/sqlite3_to_mysql/cli.py", line 187, in cli
    ).transfer()
      ^^^^^^^^^^
  File "/.venv/lib/python3.11/site-packages/sqlite3_to_mysql/transporter.py", line 665, in transfer
    self._create_table(table["name"], transfer_rowid=transfer_rowid)
  File "/.venv/lib/python3.11/site-packages/sqlite3_to_mysql/transporter.py", line 382, in _create_table
    self._mysql_cur.execute(sql)
  File "/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 1510, in execute
    res = self._connection.cmd_stmt_execute(
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/.venv/lib/python3.11/site-packages/mysql/connector/opentelemetry/context_propagation.py", line 74, in wrapper
    return method(cnx, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/.venv/lib/python3.11/site-packages/mysql/connector/connection.py", line 1459, in cmd_stmt_execute
    result = self._handle_binary_result(packet)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/.venv/lib/python3.11/site-packages/mysql/connector/connection.py", line 1349, in _handle_binary_result
    raise get_exception(packet)
mysql.connector.errors.DatabaseError: 3780 (HY000): Referencing column 'user_activity_id' and referenced column 'id' in foreign key constraint 'common_app_notification_FK_0_0' are incompatible.

Pushed command:

sqlite3mysql -f ./db.sqlite3 -d sqlite_to_mysql -u newuser --mysql-password password -h 127.0.0.1 -P 3306 --debug

Without transferring foreign key is not a solution. I want to data migrate as it is without any change into the data.

[techouse]

Without transferring foreign key is not a solution. I want to data migrate as it is without any change into the data.

I don't see why. It won't change the data.

Your problem is clearly with your DDL, as the foreign keys are incompatible for MySQL while they are fine in SQLite (which is not surprising tbh), so please supply that and we can inspect this into further detail.

[krupesh1958]

Can you please explain me bit more about Without transferring foreign key. How it's work and how my problem will solve within this?

Also, I tried to understand the problem but, i can't. Can you elaborate the problem in detail.

[techouse]

Can you please explain me bit more about Without transferring foreign key.

As the manual / help text says, you use the tool with this switch

-X, --without-foreign-keys      Do not transfer foreign keys.

How it's work and how my problem will solve within this?

You'll then have to use some DBA tool, like say DBeaver, and update your foreign keys in your MySQL database manually.