Bug #86861
closed
Database analyzer > Database analysis failed - provided SQL is OK
0%
Description
Hi,
in the install tool of my Typo3 8.7 instance, I keep getting this error when I try to run the Database analyzer:
[SQL Error] line 0, col 1335: Error: Expected NOT, NULL, DEFAULT, AUTO_INCREMENT, UNIQUE, PRIMARY, COMMENT, COLUMN_FORMAT, STORAGE or REFERENCES, got 'ON' in statement:
CREATE TABLE `tablename` ( `Kennummer` INT NOT NULL AUTO_INCREMENT, `SKZ` INT NULL DEFAULT NULL, `LAND` VARCHAR NULL DEFAULT NULL, `NAME` VARCHAR NULL DEFAULT NULL, `STRASSE` VARCHAR NULL DEFAULT NULL, `PLZ` DOUBLE NULL DEFAULT NULL, `ORT` VARCHAR NULL DEFAULT NULL, `EMAIL` VARCHAR NULL DEFAULT NULL, `URL_HTTP` VARCHAR NULL DEFAULT NULL, `URL` VARCHAR NULL DEFAULT NULL, `TYP1` VARCHAR NULL DEFAULT NULL, `TYP2` VARCHAR NULL DEFAULT NULL, `TYP3` VARCHAR NULL DEFAULT NULL, `TYP4` VARCHAR NULL DEFAULT NULL, `subtyp1` VARCHAR NULL DEFAULT NULL, `subtyp2` VARCHAR NULL DEFAULT NULL, `subtyp3` VARCHAR NULL DEFAULT NULL, `subtyp4` VARCHAR NULL DEFAULT NULL, `subtyp5` VARCHAR NULL DEFAULT NULL, `subtyp_lernmit` VARCHAR NULL DEFAULT NULL, `TEST` DOUBLE NULL DEFAULT NULL, `TESTDATUM` DATETIME NULL DEFAULT NULL, `NEU` DOUBLE NULL DEFAULT NULL, `NEUDATUM` DATETIME NULL DEFAULT NULL, `KNOTEN` DOUBLE NULL DEFAULT NULL, `KOMMENTAR` VARCHAR NULL DEFAULT NULL, `NAME2` VARCHAR NULL DEFAULT NULL, `URL2` VARCHAR NULL DEFAULT NULL, `EMAIL2` VARCHAR NULL DEFAULT NULL, `TEST2` DOUBLE NULL DEFAULT NULL, `Expositur` CHAR NULL DEFAULT NULL, `letztesedit` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `Tel1` VARCHAR NULL DEFAULT NULL, `FAX` VARCHAR NULL DEFAULT NULL, `kontakt` VARCHAR NULL DEFAULT NULL, `name_kurzform` VARCHAR NULL DEFAULT NULL, PRIMARY KEY (`Kennummer`), INDEX `URL` (`URL`), INDEX `SKZ` (`SKZ`), FULLTEXT INDEX `NAME` (`NAME`) ) COLLATE='utf8_general_ci' ENGINE=MyISAM ;
When I manually run the create query in an sql tool against the same database (only changing the tablename) everything is working as expected.
SQL more readable:
CREATE TABLE `tablename` (
`Kennummer` INT NOT NULL AUTO_INCREMENT,
`SKZ` INT NULL DEFAULT NULL,
`LAND` VARCHAR NULL DEFAULT NULL,
`NAME` VARCHAR NULL DEFAULT NULL,
`STRASSE` VARCHAR NULL DEFAULT NULL,
`PLZ` DOUBLE NULL DEFAULT NULL,
`ORT` VARCHAR NULL DEFAULT NULL,
`EMAIL` VARCHAR NULL DEFAULT NULL,
`URL_HTTP` VARCHAR NULL DEFAULT NULL,
`URL` VARCHAR NULL DEFAULT NULL,
`TYP1` VARCHAR NULL DEFAULT NULL,
`TYP2` VARCHAR NULL DEFAULT NULL,
`TYP3` VARCHAR NULL DEFAULT NULL,
`TYP4` VARCHAR NULL DEFAULT NULL,
`subtyp1` VARCHAR NULL DEFAULT NULL,
`subtyp2` VARCHAR NULL DEFAULT NULL,
`subtyp3` VARCHAR NULL DEFAULT NULL,
`subtyp4` VARCHAR NULL DEFAULT NULL,
`subtyp5` VARCHAR NULL DEFAULT NULL,
`subtyp_lernmit` VARCHAR NULL DEFAULT NULL,
`TEST` DOUBLE NULL DEFAULT NULL,
`TESTDATUM` DATETIME NULL DEFAULT NULL,
`NEU` DOUBLE NULL DEFAULT NULL,
`NEUDATUM` DATETIME NULL DEFAULT NULL,
`KNOTEN` DOUBLE NULL DEFAULT NULL,
`KOMMENTAR` VARCHAR NULL DEFAULT NULL,
`NAME2` VARCHAR NULL DEFAULT NULL,
`URL2` VARCHAR NULL DEFAULT NULL,
`EMAIL2` VARCHAR NULL DEFAULT NULL,
`TEST2` DOUBLE NULL DEFAULT NULL,
`Expositur` CHAR NULL DEFAULT NULL,
`letztesedit` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Tel1` VARCHAR NULL DEFAULT NULL,
`FAX` VARCHAR NULL DEFAULT NULL,
`kontakt` VARCHAR NULL DEFAULT NULL,
`name_kurzform` VARCHAR NULL DEFAULT NULL,
PRIMARY KEY (`Kennummer`),
INDEX `URL` (`URL`),
INDEX `SKZ` (`SKZ`),
FULLTEXT INDEX `NAME` (`NAME`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;
TIMESTAMP INITIALIZATION: https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html
Updated by Jonas Eberle about 6 years ago
The database analyzer is not able to handle any SQL in the standard. I cannot point you at all commands it is able to parse but here is an example of a typical Typo3 table:
https://docs.typo3.org/typo3cms/ExtbaseFluidBook/6-Persistence/1-prepare-the-database.html
Also the formatting is important for the database analyzer to not show any diff. You might have to play around a bit with it.
Updated by Michael Beer about 6 years ago
Jonas Eberle wrote:
The database analyzer is not able to handle any SQL in the standard. I cannot point you at all commands it is able to parse but here is an example of a typical Typo3 table:
https://docs.typo3.org/typo3cms/ExtbaseFluidBook/6-Persistence/1-prepare-the-database.html
Also the formatting is important for the database analyzer to not show any diff. You might have to play around a bit with it.
Thank you for your fast reply.
I was able to narrow it down to the
ON UPDATE CURRENT_TIMESTAMP
statement. After removing it, the db analyzer is OK with the provided SQL.
Updated by Georg Ringer about 6 years ago
- Status changed from New to Closed
thanks for creating the issue. as pointed out this file can't handle everything.
if you need the timestamp updated either update the DB manually and live with the error or define the field in the ctrl|tstamp section in the TCA and TYPO3 will update the timestamp on each edit.
I will close the issue and the ext_tables.sql will likely stay like this for a while.
Updated by Armin Vieweg over 4 years ago
Just for the records: This error also occurs, when you forget a semicolon after CREATE statement
CREATE TABLE tx_whatever ( ... );