Default value of text columns cause exception

[POPSuL]

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                                                                                         |

[techouse]

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]

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]

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

Perhaps you could spin one up in Docker?

[POPSuL]

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