Joomla is correct

https://dev.mysql.com/doc/refman/8.0/en/create-table.html

The SQL file is shipped with the extension and the error message is generated by the SQL server. The extension dev has to fix the SQL query.

I'm closing this since not a core issue.

@HLeithner I think you misunderstood. The error is from the database checker, I think, and the SQL script is a schema update of a 3rd party extension, as you can see by the script name. The database checker does not understand the optional IF NOT EXISTS keywords in a CREATE TABLE statement before the table name, but it is valid SQL for both MySQL and MariaDB. It’s just the database checker (MySQL change item) which currently doesn’t understand that. I am working on a PR to change that, but it might take a bit time. Nevertheless right. Ow it is expected (but undocumented) behavior.

@brianteeman As you can see in the docs you have linked and could see in the equivalent docs for MariaDB, the optional keywords IF NOT EXISTS before the table name are valid SQL for these databases (as well as PostgreSQL). The author of the issue just hasn’t posted the complete statement. He has omitted the table name.

We should never have made the db checker check also 3rd party extensions in J4. Just my 5 ct.

The author of the issue just hasn’t posted the complete statement. He has omitted the table name.

Your ability to read invisible ink is obviously better than mine :)

ok in this case please fix it ;-)

Hmm, I might have to correct myself. The MySQL change item handles that, too: https://github.com/joomla/joomla-cms/blob/4.0-dev/libraries/src/Schema/ChangeItem/MysqlChangeItem.php#L213-L227

So it seems the SQL statement in the SQL script really is incomplete.

@fredhob Could you post here the complete SQL statement from that file 1.0.0.sql so we can see what the error is? Thanks in advance.

I was maybe confused by a similar recent issue with other optional keyword which was really not supported by the checker.

ok no problem thanks for rechecking, in this case I close it until @fredhob provides a complete sql script so we can reproduce the error.

Ok i understand the "problem" in my sql script, no space between EXISTS and the table name:

CREATE TABLE IF NOT EXISTS#__table

And Joomla 4 don't like it

Ok i understand the "problem" in my sql script, no space between EXISTS and the table name:

CREATE TABLE IF NOT EXISTS#__table

And Joomla 4 don't like it

Thanks for reporting back. Joomla is right at this point, without the space it's not valid syntax. The database may be tolerant with that and not throw an SQL error, depending on server settings, but we should not try to adapt our database checker to such tolerance of invalid SQL.