SQL syntax error (19.10.12)

[rvhelp]

I get an SQL syntax error in the update script:

Checking table "mshop_subscription":
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 'SMALLINT DEFAULT 0 NOT NULL, CHANGE siteid siteid INT DEFAULT NULL, CHANGE ne...' at line 1: ALTER TABLE mshop_subscription ADD productid VARCHAR(36) DEFAULT '' NOT NULL, ADD period SMALLINT DEFAULT 0 NOT NULL, CHANGE siteid siteid INT DEFAULT NULL, CHANGE next next DATE DEFAULT NULL, CHANGE end end DATE DEFAULT NULL, CHANGE `interval` `interval` VARCHAR(32) NOT NULL, CHANGE reason reason SMALLINT DEFAULT NULL

I had foreign key errors and disabled foreign key checks to get ahead (also in the previous version 18.10.11).

What version of MariaDB should be used or is it a different problem?

• Aimeos 19.20.12

• Typo 9.5.31

• Mysql Ver 15.1 Distrib 10.5.15-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

• Php PHP 7.4.13 (cli) (built: Nov 30 2020 10:40:38) ( NTS )

• Ubuntu 18.04.4 LTS

[rvhelp]

deleted the table (it was empty) and it worked

[aimeos]

Regarding failed foreign key constraints:
You've used the TYPO3 Database Analyzer in the past and let it remove all foreign key constraints because it doesn't support them. Then, there will be left over records in the mshop_*_list and mshop_*_property tables (and a few other ones) after deleting entries in the main tables because they are automatically removed with working FK-Constraints. To fix your setup, you have to remove all records with no parent record available, e.g.:

Code: Select all

DELETE FROM mshop_product_list pl WHERE NOT EXISTS (SELECT id FROM mshop_product WHERE p.id=pl.parentid)

Repeat that for all tables until no errors are reported any more.

[rvhelp]

Thanks i will try that.

[aimeos]

We haven't seen that kind of problem before so if you have a fix, please share