Installation Using Maria DB for Fusio API repository generates errors.

[kvnlwlls]

Attempting to use Maria DB (windows binary version: 5.5.5-10.4.6-MariaDB) for Fusio API repository generates errors during installation (installation error output shown below).

Running 64-bit Apache 2.4.25 (Win64) with PHP/7.3.0 on Windows 7 Professional (Service Pack 1)

The 'psx_connection' attribute in file 'configuration.php' is defined as follows:

'psx_connection'          => [
    'dbname'              => getenv('FUSIO_DB_NAME'),
    'user'                => getenv('FUSIO_DB_USER'),
    'password'            => getenv('FUSIO_DB_PW'),
    'host'                => getenv('FUSIO_DB_HOST'),
    'port'                => getenv('FUSIO_DB_PORT'),
    'driver'              => 'pdo_mysql',
    'driverOptions'       => [
        // dont emulate so that we can use prepared statements in limit clause
        \PDO::ATTR_EMULATE_PREPARES => false
    ],
],

[chriskapp]

Hi, thanks for the info, it looks like this a problem regarding Doctrine DBAL since currently only version 10.2 is supported, please take a look at the issue: doctrine/dbal#3278
We currently use version v2.5.13 of DBAL sicne this is also used by Symfony but in the future we want to update to the latest 3.0 version (if its released) which then hopefully also supports the latest MariaDB version.

[chriskapp]

With the next 1.8 release we will update doctrine/dbal to 2.9, then it should be also possible to use the latest MariaDB version.

[ratsey]

@chriskapp I'm getting the same error with an installation on MariaDB 10.4.8. I have Fusio 1.8.0 installed - was this not resolved or am I making an error with my install? I tried manually installing and also with the web installer - same error with both.

[kvnlwlls]

@chriskapp I'm getting the same error with an installation on MariaDB 10.4.8. I have Fusio 1.8.0 installed - was this not resolved or am I making an error with my install? I tried manually installing and also with the web installer - same error with both.

Please provide a screen shot of the installation errors you are receiving.

[ratsey]

This on running through the install.php file. Everything seems fine until this part. It's too much for a screen shot, hope this is sufficient.

It seems that the correct syntax that MariaDB wants is

ALTER TABLE fusio_plan ADD COLUMN period INT DEFAULT NULL

++ migrating 20190416200104

     -> CREATE TABLE fusio_user_attribute (id INT AUTO_INCREMENT NOT NULL, user_id INT NOT NULL, name VARCHAR(255) NOT NULL, value VARCHAR(255) NOT NULL, INDEX IDX_959096FAA76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB
     -> CREATE TABLE fusio_plan_contract (id INT AUTO_INCREMENT NOT NULL, user_id INT NOT NULL, plan_id INT NOT NULL, status INT NOT NULL, amount NUMERIC(8, 2) NOT NULL, points INT NOT NULL, period INT DEFAULT NULL, insert_date DATETIME NOT NULL, INDEX IDX_5D691BF5A76ED395 (user_id), INDEX IDX_5D691BF5E899029B (plan_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB
     -> CREATE TABLE fusio_plan_invoice (id INT AUTO_INCREMENT NOT NULL, contract_id INT NOT NULL, user_id INT NOT NULL, prev_id INT DEFAULT NULL, display_id VARCHAR(255) NOT NULL, status INT NOT NULL, amount NUMERIC(8, 2) NOT NULL, points INT NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, pay_date DATETIME DEFAULT NULL, insert_date DATETIME NOT NULL, INDEX IDX_7DE88CB82576E0FD (contract_id), INDEX IDX_7DE88CB8A76ED395 (user_id), INDEX IDX_7DE88CB8B168B8C0 (prev_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB
     -> ALTER TABLE fusio_user_attribute ADD CONSTRAINT user_attribute_user_id FOREIGN KEY (user_id) REFERENCES fusio_user (id)
     -> ALTER TABLE fusio_plan_contract ADD CONSTRAINT plan_contract_user_id FOREIGN KEY (user_id) REFERENCES fusio_user (id)
     -> ALTER TABLE fusio_plan_contract ADD CONSTRAINT plan_contract_plan_id FOREIGN KEY (plan_id) REFERENCES fusio_plan (id)
     -> ALTER TABLE fusio_plan_invoice ADD CONSTRAINT plan_invoice_contract_id FOREIGN KEY (contract_id) REFERENCES fusio_plan_contract (id)
     -> ALTER TABLE fusio_plan_invoice ADD CONSTRAINT plan_invoice_user_id FOREIGN KEY (user_id) REFERENCES fusio_user (id)
     -> ALTER TABLE fusio_plan_invoice ADD CONSTRAINT plan_invoice_prev_id FOREIGN KEY (prev_id) REFERENCES fusio_plan_invoice (id)
     -> ALTER TABLE fusio_plan ADD period INT DEFAULT NULL
Migration 20190416200104 failed during Execution. Error An exception occurred while executing 'ALTER TABLE fusio_plan ADD period INT DEFAULT NULL':

SQLSTATE[42000]: Syntax error or access violation: 1064 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 'INT DEFAULT NULL' at line 1

In AbstractMySQLDriver.php line 79:
                                                                                                                       
  An exception occurred while executing 'ALTER TABLE fusio_plan ADD period INT DEFAULT NULL':                          
                                                                                                                       
  SQLSTATE[42000]: Syntax error or access violation: 1064 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 'INT DEFAULT NULL' at line 1            
                                                                                                                       

In PDOConnection.php line 90:
                                                                                                                       
  SQLSTATE[42000]: Syntax error or access violation: 1064 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 'INT DEFAULT NULL' at line 1            
                                                                                                                       

In PDOConnection.php line 88:
                                                                                                                       
  SQLSTATE[42000]: Syntax error or access violation: 1064 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 'INT DEFAULT NULL' at line 1            
                                                                                                                       

migration:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--connection CONNECTION] [--] [<version>]

[ratsey]

I appreciate this isn't a support forum, but can this be confirmed as an issue before I start trying to work out what I'm doing wrong? If indeed an issue, is there a workaround, such as running the DB setup script manually and adding the word 'COLUMN' to the ALTER TABLE statements?

[ratsey]

Looking over the MariaDB documentation, the provided syntax of ALTER TABLE fusio_plan ADD period INT DEFAULT NULL is legal. The problem is that period is a keyword [ref. https://mariadb.com/kb/en/library/reserved-words/], but only when SQL_MODE = ORACLE [https://mariadb.com/kb/en/library/sql_modeoracle-from-mariadb-103/].

So either of these would work in my scenario:

ALTER TABLE fusio_plan ADD COLUMN period INT DEFAULT NULL

ALTER TABLE fusio_plan ADD `period` INT DEFAULT NULL

Now I just need to work out which of the SQL_MODE settings that SQL_MODE=ORACLE is switching on, so that I can switch it off. Bottom line, it would appear this is not a bug but a 'feature' of how MariaDB is setup. I'll report back here if/when I find the solution in case anyone else should stumble on this same error and post.

Update: Seems like none of the SQL_MODE settings has any effect. Clearing them all out gives the same error. Unsure how to proceed.

[chriskapp]

Hi, so to fix this you could use the php bin/fusio install command with the option --write-sql, to get all SQL queries which are executed on installation, then you could alter this specific statement. In general we want to support as many databases as possible so we might rename the period column if this is the problem. As far as I know doctrine provides no way to enable quoting around identifiers. But Iam currently not sure whats the best solution since all databases Mysql, Postgres and Sqlite work with this setup.

[ratsey]

Not sure I understand correctly. I thought perhaps php bin/fusio install --write-sql would just dump out the SQL that I would then run manually. But it seems that it's just writing the output to the command line and still stops at the same error, executing the SQL as it progresses. Is that the correct syntax to add the option?

[chriskapp]

Normally it should write the SQL to a file, you can also specify the concrete file i.e. php bin/fusio install --write-sql=my.sql so the command looks fine. You can also use the --dry-run option to prevent any SQL execution. You can find more infos about the available options through php bin/fusio help install

[titzu]

Hi,
I had the same problem. I solved it like that:

1. php bin/fusio install
2. manually execute ALTER TABLE fusio_plan ADD `period` INT DEFAULT NULL
3. deleted fusio_plan_contract, fusio_user_attribute and fusio_plan_invoice that were generated by the failed install
4. bin/fusio migration:execute 20190416200104 (this step is not necessary because it will be executed anyway by step 4 but this is how I did it...)
5. bin/fusio migration:migrate

[linuxmalaysia]

Thanks, this help to resolved issue #277

Hi,
I had the same problem. I solved it like that:

1. php bin/fusio install

2. manually execute `` ALTER TABLE fusio_plan ADD `period` INT DEFAULT NULL ``

3. deleted fusio_plan_contract, fusio_user_attribute and fusio_plan_invoice that were generated by the failed install

4. bin/fusio migration:execute 20190416200104 (this step is not necessary because it will be executed anyway by step 4 but this is how I did it...)

5. bin/fusio migration:migrate

[chriskapp]

We have finally changed the column name on the table from period to period_type, the next release contains this fix. So this should then solve theses problems on mariadb.

[ronnyvillamar99]

I have the same problem

[ronnyvillamar99]

thanks, problem solved.

Hi,
I had the same problem. I solved it like that:

1. php bin/fusio install

2. manually execute `` ALTER TABLE fusio_plan ADD `period` INT DEFAULT NULL ``

3. deleted fusio_plan_contract, fusio_user_attribute and fusio_plan_invoice that were generated by the failed install

4. bin/fusio migration:execute 20190416200104 (this step is not necessary because it will be executed anyway by step 4 but this is how I did it...)

5. bin/fusio migration:migrate

[chriskapp]

So I will close this issue since the issue is solved