Joomla 4 not accepting SQL in component.

[darrenf...@gmail.com]

Hi,

I've got a problem with a component that I'm making - I've added an SQL file with the following text in it..

DROP TABLE IF EXISTS `#__shop_options` ;

CREATE TABLE IF NOT EXISTS `#__shop_optiongroups` (

  `OptionGroupID` int(11) NOT NULL AUTO_INCREMENT,

  `OptionGroupName` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,

  PRIMARY KEY (`OptionGroupID`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=4 ;

When installing the component it installs the component but just wont create the database - if I then try and just enter the code using the execute function of the Database class it gives me an SQL error about there being a syntax error for MariaDB (something about 'near')

I've put this same code into PhpMyAdmin (replacing #_ with the database extension) and it created the table fine (the actual code came from a phpmyadmin export!).

Does anyone know why Joomla 4's SQL has an issue with this but PhpMyAdmin can create the table fine (there is other tables in my code too - but the error seems to be occurring on the first "DROP TABLE IF EXISTS" statement - if I remove the DROP TABLE statement and just have it as CREATE TABLE IF NOT EXISTS...  I get the same error - and if I remove more SQL statements the error just moves on to the next statement.

Am I missing something and why does Phpmyadmin accept it but not Joomla - surely Phpmyadmin is running the same SQL code as Joomla??

[Mark Stanton]

You almost always have to create the database yourself, because most frequently Joomla won't, and then the component installation can create the tables.

I suspect hosting companies are granting rights to phpMyAdmin that the webserver doesn't have the rest of the time.

Of course, if this is your own local server, then I don't know.  Because this is (almost?) always the way it goes on hosted platforms I create databases myself locally before installation just in case, so I've never seen what happens if I don't.

[Mark Fleeson]

Hi Darren

The SQL looks correct and works on both mysql and mariadb. Have you put a use statement before the start of your SQL? The fact that you have a working installation of Joomla to run the install component means the default database has been created, so you can ignore that comment above and also assuming you can create articles, menus etc in the Joomla installation your webserver will have access rights.

Are you using an installer script? Can you share the top part of the SQL script?

Best Wishes

Mark

Joomla Extensions Directory Team Leader.

[darrenf...@gmail.com]

Hi Mark,

I finally solved the problem - it was amazingly simple and yet nothing to do with the SQL!!

The error was in the XML file - I'd forgot <folder>sql</folder> in the administrator section - so it wasn't installing the sql files!  Don't quite understand how it was creating an SQL error rather than file not found error - but hey it works now!