Airsonic: Invalid SQL syntax while creating tables using MySQL database

[flesniak]

Problem description

When creating a new database using a MariaDB server, airsonic tries to use VARCHAR without a length which is invalid syntax for MySQL. This error is printed:

```ERROR --- liquibase.changelog.ChangeSet: Change Set classpath:liquibase/legacy/schema25.xml::schema25_003::muff1nman failed.  Error: 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 'NOT NULL, CONSTRAINT PK_ROLE PRIMARY KEY (id))' at line 1 [Failed SQL: CREATE TABLE airsonic.`role` (id INT AUTO_INCREMENT NOT NULL, name VARCHAR NOT NULL, CONSTRAINT PK_ROLE PRIMARY KEY (id))]```

It seems like liquibase does not use the varchar_type for mysql, as set here: https://github.com/airsonic/airsonic/blob/master/airsonic-main/src/main/resources/liquibase/db-changelog.xml
The query works if I change VARCHAR to VARCHAR(255) and execute it manually.

Steps to reproduce

1. Configure a fresh airsonic installation to use a mysql server via jdbc like shown below
2. Start the server

System information

• Airsonic version: 10.3.1
• Operating system: Arch Linux
• Java version: Java: 1.8.0_212 (jre8-openjdk 8.u212-1)
• SQL server: mysqld Ver 10.3.15-MariaDB

Additional notes

DatabaseMysqlMaxlength=255
DatabaseConfigType=EMBED
DatabaseConfigEmbedDriver=com.mysql.jdbc.Driver
DatabaseConfigEmbedPassword=password
DatabaseConfigEmbedUrl=jdbc:mysql://localhost:3306/airsonic
DatabaseConfigEmbedUsername=airsonic
DatabaseUsertableQuote=

[jvoisin]

We should delete the DatabaseMysqlMaxlength and simply hardcode this value. Why is it configurable in the first place?

[flesniak]

While hardcoding that value seems fine for me, I don't think it will fix the actual issue. I guess that liquibase would still not properly select the proper mysql-syntax vor varchar, even if we replace it like that:

<property name="varchar_type" dbms="mysql" value="varchar(512)" />
<property name="varchar_type" value="varchar" />

It seems like the mechanism for selecting the special varchar_type when dbms equals mysql is broken and thus always selects the fall-back value. I'm wondering if it is maybe confused by the MariaDB server instead of MySQL?
I've been digging into the liquibase source, but I can't find where the db-changelog.xml file is actually parsed. Maybe that could give a hint.

[jvoisin]

Let me try to summon @muff1nman in this thread, since he is, amongst other things, our resident liquibase wizard!

[cjazinski]

I'm having the same issue.

[Celedhrim]

Hi,

Just testing 10.4.0 here :

Debian 09 stretch with mysql Ver 15.1 Distrib 10.1.38-MariaDB
Debian 10 buster with mysql Ver 15.1 Distrib 10.3.15-MariaDB

For both , airsonic fail to initialize BD

[Celedhrim]

Pull request done according
https://artofcode.wordpress.com/2018/12/01/executing-sql-by-liquibase-on-mariadb/

Test with success here ! now db create without issue !