Error running migration" error="failed to run migration from 0.2.0 to 0.3.0: failed to run transaction: failed to execute migration 0.2.0 to 0.3.0: Error 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'config' can't have a default value"

[huyangkkk]

Data

Shiori version 1.7.1 (build 2315f0d)

Describe the bug / actual behavior

databases: mysql
version:8.0.36
os:ubuntu

time="2024-11-11T17:31:03+08:00" level=fatal msg="Error running migration" error="failed to run migration from 0.2.0 to 0.3.0: failed to run transaction: failed to execute migration 0.2.0 to 0.3.0: Error 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'config' can't have a default value"

Expected behavior

start Shiori

[fmartingr]

Which version of the mysql/mariadb server are you using?

[huyangkkk]

mysql version:8.0.36

[sandeepkumar1101]

Any update i am also facing same issue

mysql version:8.0.36

[Varamil]

Same kind of error on my side, with slightly different error message:
time="2025-01-01T14:53:42+01:00" level=fatal msg="Error running migration" error="failed to run migration from 0.2.0 to 0.3.0: failed to run transaction: failed to execute migration 0.2.0 to 0.3.0: Error 1064 (42000): 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 'JSON NOT NULL DEFAULT '{}',\n\t\tPRIMARY KEY (id),\n\t\tUNIQUE KEY account_u' at line 6"

Shiori 1.7.2 on windows 11.
mysql 8.0.30

[fmartingr]

Found the issue causing this, will be fixed in 1.7.3 which I plan to release today.

[Varamil]

Still same issue with 1.7.3. Information I haven't mention is the database is empty when shiori is started and the error raise.

[fmartingr]

I've run the test suite locally using MySQL 8.0.40 which executes the entire migration set per test and didn't get any errors. That was the way I reproduced it originally, and now it should work for MySQL 8.0.13+.

Are you getting the same error or a new one?

[Varamil]

Yes exactly the same error:
time="2025-01-03T09:22:54+01:00" level=fatal msg="Error running migration" error="failed to run migration from 0.2.0 to 0.3.0: failed to run transaction: failed to execute migration 0.2.0 to 0.3.0: Error 1064 (42000): 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 'JSON NOT NULL DEFAULT ('{}'),\n\t\tPRIMARY KEY (id),\n\t\tUNIQUE KEY account' at line 6"

Shiori 1.7.3 on windows 11.
mysql 8.0.30, database empty when running shiori
Command used: .\shiori.exe server --secret-key "SecretKey" -p 8081
SHIORI_DATABASE_URL="mysql://user:pass@tcp(address)/databaseName?charset=utf8mb4"

[fmartingr]

Yes exactly the same error: time="2025-01-03T09:22:54+01:00" level=fatal msg="Error running migration" error="failed to run migration from 0.2.0 to 0.3.0: failed to run transaction: failed to execute migration 0.2.0 to 0.3.0: Error 1064 (42000): 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 'JSON NOT NULL DEFAULT ('{}'),\n\t\tPRIMARY KEY (id),\n\t\tUNIQUE KEY account' at line 6"

Shiori 1.7.3 on windows 11. mysql 8.0.30, database empty when running shiori Command used: .\shiori.exe server --secret-key "SecretKey" -p 8081 SHIORI_DATABASE_URL="mysql://user:pass@tcp(address)/databaseName?charset=utf8mb4"

Are you sure you are running MySQL 8.0.30? Your error log says "MariaDB".

[Varamil]

Hum, you're right. The documentation of my host was unclear, sorry. However, I was able to switch to a MySQL database, but it was only on version 5.5.40, which looks quite old. I don't know what is the minimum version required for Shiori ?

By the way, I've tried to run Shiori, just to see, and I get the same error:
time="2025-01-05T07:47:22+01:00" level=fatal msg="Error running migration" error="failed to run migration from 0.2.0 to 0.3.0: failed to run transaction: failed to execute migration 0.2.0 to 0.3.0: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JSON NOT NULL DEFAULT ('{}'),\n\t\tPRIMARY KEY (id),\n\t\tUNIQUE KEY account' at line 6"
except MariaDB is replaced by MySQL.

[fmartingr]

Hum, you're right. The documentation of my host was unclear, sorry. However, I was able to switch to a MySQL database, but it was only on version 5.5.40, which looks quite old. I don't know what is the minimum version required for Shiori ?

By the way, I've tried to run Shiori, just to see, and I get the same error: time="2025-01-05T07:47:22+01:00" level=fatal msg="Error running migration" error="failed to run migration from 0.2.0 to 0.3.0: failed to run transaction: failed to execute migration 0.2.0 to 0.3.0: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JSON NOT NULL DEFAULT ('{}'),\n\t\tPRIMARY KEY (id),\n\t\tUNIQUE KEY account' at line 6" except MariaDB is replaced by MySQL.

Yeah, for MySQL you need to run 8.0.13+. Unsure about the parity with MariaDB, but I just ran the test suite with 10.5.27 (the oldest supported release) and it completed successfully.

[Varamil]

OK thanks, sorry again for the trouble.

[fmartingr]

Don't worry, I'm going to update the CI to fire up every database engine on the oldest supported release so we catch this things beforehand. 👍