SQL syntax error (19.10.12)
Forum rules
Always add your TYPO3, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
Always add your TYPO3, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
SQL syntax error (19.10.12)
I get an SQL syntax error in the update script:
What version of MariaDB should be used or is it a different problem?
I had foreign key errors and disabled foreign key checks to get ahead (also in the previous version 18.10.11).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
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
Re: SQL syntax error (19.10.12)
deleted the table (it was empty) and it worked
Re: SQL syntax error (19.10.12)
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.:
Repeat that for all tables until no errors are reported any more.
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)
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star
Re: SQL syntax error (19.10.12)
Thanks i will try that.
Last edited by rvhelp on 14 Sep 2022, 11:21, edited 4 times in total.
Re: SQL syntax error (19.10.12)
We haven't seen that kind of problem before so if you have a fix, please share
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star