Skip to content

Default value of text columns cause exception #91

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
POPSuL opened this issue Nov 24, 2024 · 4 comments
Open

Default value of text columns cause exception #91

POPSuL opened this issue Nov 24, 2024 · 4 comments
Assignees
Labels
bug Something isn't working mariadb MariaDB specific

Comments

@POPSuL
Copy link

POPSuL commented Nov 24, 2024

Describe the bug
Table with that definition cause exception:

CREATE TABLE `test` (
  `test` text NOT NULL DEFAULT '[]'
) ENGINE=InnoDB;

Expected behaviour
Successful table creation

Actual result

mysql2sqlite version 2.3.0 Copyright (c) 2019-2024 Klemen Tusar
2024-11-24 18:52:15 INFO     Transferring table test
2024-11-24 18:52:16 ERROR    SQLite failed creating table test: near "[]": syntax error
Traceback (most recent call last):
  File "/root/.local/bin/mysql2sqlite", line 8, in <module>
    sys.exit(cli())
             ^^^^^
  File "/root/.local/share/pipx/venvs/mysql-to-sqlite3/lib/python3.12/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/share/pipx/venvs/mysql-to-sqlite3/lib/python3.12/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/root/.local/share/pipx/venvs/mysql-to-sqlite3/lib/python3.12/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/share/pipx/venvs/mysql-to-sqlite3/lib/python3.12/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/share/pipx/venvs/mysql-to-sqlite3/lib/python3.12/site-packages/mysql_to_sqlite3/cli.py", line 230, in cli
    converter.transfer()
  File "/root/.local/share/pipx/venvs/mysql-to-sqlite3/lib/python3.12/site-packages/mysql_to_sqlite3/transporter.py", line 700, in transfer
    self._create_table(table_name)  # type: ignore[arg-type]
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/share/pipx/venvs/mysql-to-sqlite3/lib/python3.12/site-packages/mysql_to_sqlite3/transporter.py", line 568, in _create_table
    self._sqlite_cur.executescript(self._build_create_table_sql(table_name))
sqlite3.OperationalError: near "[]": syntax error

System Information

$ mysql2sqlite --version
| software               | version                                                                                        |
|------------------------|------------------------------------------------------------------------------------------------|
| mysql-to-sqlite3       | 2.3.0                                                                                          |
|                        |                                                                                                |
| Operating System       | Linux 5.15.153.1-microsoft-standard-WSL2                                                       |
| Python                 | CPython 3.12.3                                                                                 |
| MySQL                  | mysql  Ver 15.1 Distrib 10.11.8-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper |
| SQLite                 | 3.45.1                                                                                         |
|                        |                                                                                                |
| click                  | 8.1.7                                                                                          |
| mysql-connector-python | 9.1.0                                                                                          |
| python-slugify         | 8.0.4                                                                                          |
| pytimeparse2           | 1.7.1                                                                                          |
| simplejson             | 3.19.3                                                                                         |
| tabulate               | 0.9.0                                                                                          |
| tqdm                   | 4.67.0                                                                                         |
@POPSuL POPSuL added the bug Something isn't working label Nov 24, 2024
@techouse
Copy link
Owner

techouse commented Nov 24, 2024

Describe the bug Table with that definition cause exception:

CREATE TABLE `test` (
  `test` text NOT NULL DEFAULT '[]'
) ENGINE=InnoDB;

MySQL does not support that sort of syntax. According to https://dev.mysql.com/doc/refman/8.4/en/data-type-defaults.html you'd have to write something like

CREATE TABLE `test`(
   `test` text NOT NULL DEFAULT ('[]')
) ENGINE=InnoDB;

Can you test with that and report back?

@POPSuL
Copy link
Author

POPSuL commented Nov 25, 2024

MySQL does not support that sort of syntax.

Ohh, you're right!
I didn't pay attention to the fact that that mariadb differs from mysql in these regards...
But I can't test it with mysql 'cause I've only mariadb instance. 🤷‍♂️

@techouse
Copy link
Owner

But I can't test it with mysql 'cause I've only mariadb instance. 🤷‍♂️

Perhaps you could spin one up in Docker?

@POPSuL
Copy link
Author

POPSuL commented Nov 25, 2024

Perhaps you could spin one up in Docker?

Yes, I checked it in Docker with mysql:8. It works, successfully creates sqlite with the schema:

CREATE TABLE IF NOT EXISTS "test" (
        "test" TEXT NOT NULL DEFAULT '[]'
);

But unfortunately, it won't help me, because my source database is in MariaDB, and I won't be able to convert it to mysql for the same reason (schema is not compatible with mysql).
I'll try to find an alternative solution, or write it myself.

Sorry for bothering you,
Have a nice day

@techouse techouse added mariadb MariaDB specific and removed need more info labels Feb 26, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working mariadb MariaDB specific
Projects
None yet
Development

No branches or pull requests

2 participants