SQL works in phpmyadmin but not in component install script

[Roger CO]

I have a install sql script for my component which works fine standalone but not when it is run during the component installation process. (in file sql/install.mysql.utf8.sql)

I am adding a column (catid) to an existing table if it doesn't exist. The SQL is:
================
CODE: SELECT ALL

SET @dbname = DATABASE();
SET @tablename = "#__targettable";
SET @columnname = "catid";
SET @preparedStatement = (SELECT IF(
(
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE
(table_name = @tablename)
AND (table_schema = @dbname)
AND (column_name = @columnname)
) > 0,
"SELECT 1",
CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT NOT NULL DEFAULT '0' COMMENT 'used by com_rco' AFTERlanguage;")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
================
This works fine when run in phpMyAdmin, but in the install script it fails with "Extension Install: SQL error processing query: DB function failed with error number 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 SQL = PREPARE alterIfNotExists FROM @preparedStatement;"

Can you see the error? Is it even possible to do this (check if a column exists before altering the table), if so how? If you don't check then the install of the component will fail if the column does already exist (error number 1060 Duplicate column name 'catid').

[Roger CO]

I should add that the lack of a space in "AFTERlanguage" in the code quoted is a copy-paste typo here, not the problem - 

the original script has "AFTER language;"

[dbsaul]

When joomla runs the install script does the joomla dB connection/user have the same permissions as you phpAdmin user?

Sent from my iPhone

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.
To post to this group, send email to joomla-de...@googlegroups.com.
Visit this group at https://groups.google.com/group/joomla-dev-general.
For more options, visit https://groups.google.com/d/optout.

[Roger at Gmail]

That’s a very good question. As far as I know it does - the Joomla DB user has “all privileges” on the database but it occurs to me that the PREPARE function might need system level access elsewhere?

I have just tried running it on a live site in case the problem was specific to my test server but I get the same result. The actual error message text from the Joomla installer is this

×

Warning

JInstaller: :Install: Error SQL You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Extension Install: SQL error processing query: DB function failed with error number 1064 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
SQL =

PREPARE alterIfNotExists FROM @preparedStatement;

As I understand it 1064 is a syntax error and "near ‘’ at line” usually means an unexpected end of command was encountered. 

Is Joomla failing to pass all of the SET statements before the “PREPARE” line? I would count the PREPARE line as being line 5. 

RogerCO

[Mario Lacunza]

Run an echo command and check the SQL generated...

Saludos / Best regards

Mario Lacunza
Email:: mlac...@gmail.com
Personal Website:: http://www.lacunza.biz/
Hosting:: http://mlv-host.com/
Skype: mlacunzav

Lima - Peru

[Roger CO]

Thank you everyone for your helpful replies - they put me on the right track.

The answer seems to be that you can't use SET @tablename = "#__table_you_want_to_edit"; because the "#_" doesn't get replaced by the Joomla table prefix. The install sql file is just executed as written and not touched by Joomla. Hence the prepared statement fails as it can't find the table. 

So there must be another way of doing this.

To recap - when updating a component I want to alter a table by adding a column if it doesn't exist. You can do this in SQL using the prepared statement to test if the column exists before trying to add it and thus avoid generating an error. But how can I get the Joomla table prefix into the SQL? It seems like I will have to do it in the php script. What is the current best way of executing some sql from php in Joomla?

[Mike eco]

Hi,

In the install script or update scripts you don't have to select a database and write all the sql code.

for example in my update sxript I have this :

ALTER TABLE `jdev_mycom_users` ADD `role` tinyint(1) NOT NULL DEFAULT '0' AFTER `id`;

where jdev is my table prefix and with this single line works fine.

[Mike eco]

I forgot to mention that if your extension is already installed you have to place the sql code in a update script that corresponds to the version of your main xml file. The install script runs only on the 1st time install of your component, then the update scripts do the job.

take a look at the joomla docs the "Modify the SQL file" : 

https://docs.joomla.org/J3.x:Developing_an_MVC_Component/Adding_categories#admin.2Fsql.2Fupdates.2Fmysql.2F0.0.12.sql

[Roger at Gmail]

That is fine but you have to know the table prefix which is specific to the Joomla installation and not under your control.

RogerCO

[Roger at Gmail]

Using the update script for the version where you want to add the column and is ok but there are two problems:

1. Various sequences of user actions can result in the install or update script attempting to alter the table when the column already exists thus creating an error and failure of the install. (eg user runs the full install package for a new version after a version which has already updated the table structure, or the user skips the update that altered the table and then runs the next update which would assume the column had already been added)

2. In my case I am altering a table that is not under the control of my component - adding a column to link another component to my extension data - and so I can’t be sure whether or not the table has already had the column added, or I might want to test if a different column exists before adding my own.

I think it has to be a php solution.

Roger

[Mike eco]

You are right, that was a copy paste and forgot to change.

In the link I mentioned in the previous post there is an example how to modify the table and is with `#__` and not the prefix.

ALTER TABLE `#__helloworld` ADD `catid` int(11) NOT NULL DEFAULT '0';

Read the doc page and check it out

https://docs.joomla.org/J3.x:Developing_an_MVC_Component/Adding_categories#admin.2Fsql.2Fupdates.2Fmysql.2F0.0.12.sql

Mike

[Roger at Gmail]

Yes you are right - the #__tablename syntax seems to work ok in simple SQL statements, but when used in SET @targettable=“#__tablename” it doesn’t.

Since the simple version works Joomla must be doing some processing of the script file rather than passing it straight to the database, but it seems to fail when the #__tablename is in a SET statement.

I have tried both double and single quotes and also without quotes around it.

With no quotes the SQL fails with “Unknown column ‘#__tablename’ which makes sense as Joomla isn’t touching it and SQL thinks it must be a column name not a string since it hasn’t got quotes around it.

With either type of quote you get the original error which presumably is when SQL tries to parse the CONCAT statement that includes @tablename as a non existent table. Being hidden in a prepared statement the error message is a little obscure.

So the question is whether this is a flaw in the design of the Joomla installer. It is not universally replacing “#_” with the table prefix, only if it is in the body of a simple statement like SELECT ALTER INSERT UPDATE or DROP?

The reason for using a prepared statement is that is AFAIK the only way to test if a column exists without having to create a stored procedure on the database. (This site is still on MySQL5.7, v8 might have a better solution)

Perhaps this discussion needs to move to bugs? It seems to me that Joomla should replace #_ with the prefix wherever it appears.

Thanks for considering the problem

Roger

[Hannes Papenberg]

Not saying that this is not a bug, but just pointing out that you don't
want #__ replaced with the prefix everywhere. If it is actual content
for the table, you don't want this replaced, otherwise #__ would also be
replaced in the documentation for the database system in Joomla for
example. ;-)

Am 11.09.2018 um 16:52 schrieb 'Roger at Gmail' via Joomla! General
Development:

>> <mailto:mixa...@gmail.com>> wrote:
>>
>> You are right, that was a copy paste and forgot to change.
>>
>> In the link I mentioned in the previous post there is an example how
>> to modify the table and is with `#__` and not the prefix.
>>
>> ALTER TABLE `#__helloworld` ADD `catid` int(11) NOT NULL DEFAULT '0';
>>
>> Read the doc page and check it out
>> https://docs.joomla.org/J3.x:Developing_an_MVC_Component/Adding_categories#admin.2Fsql.2Fupdates.2Fmysql.2F0.0.12.sql
>>
>> Mike
>>
>

> --
> You received this message because you are subscribed to the Google
> Groups "Joomla! General Development" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to joomla-dev-gene...@googlegroups.com

> <mailto:joomla-dev-gene...@googlegroups.com>.

> To post to this group, send email to joomla-de...@googlegroups.com

> <mailto:joomla-de...@googlegroups.com>.

[Roger at Gmail]

Yes that is certainly true. It did occur to me that perhaps it was ignoring it because it was in quotes, but I tried it without the quotes in both the ALTER and the SET statements and in ALTER it is touched but in SET it isn’t.

Which of course is correct for this:

UPDATE #__tablename SET content=“#__this is some content” WHERE id=1

needs to be:

UPDATE prefix_tablename SET content="#__this is some content” WHERE id=1

not

UPDATE prefix_tablename SET content=“prefix_this is some content” WHERE id=1

So in this (most) cases ignoring the #_ after a SET where it is in quotes would be correct.

I can’t see any easy way to work around this within mysql. It’ll have to be a php solution to test if a column exists.

[Mathew Lenning]

Yeah the native Joomla sql handler unfortunately makes mistakes costly. 

Couple points that might help.

• You don't need to add the check in your sql/install.mysql.utf8.sql as this is only run on new installs. 
• Instead of adding the sql to an update file (e.g. sql/mysql/0.0.2.sql) you could put it in sql/mysql folder then handle its processing in the installer script.

The benefit here is you can check the version before executing it. Here is a snippet from my installer to give you an idea of what I mean

$extensionVersion = $this->getCurrentVersion($parent);

if (is_null($extensionVersion))
{
 return;
}

$newVersion = (string) $parent->getManifest()->version;

if (version_compare($extensionVersion->getVersion(), (string) $newVersion, '>'))
{
 //do something
}

You can execute the SQL by first loading it into memory and using joomla dbo to execute it. You won't need the prepared statement


$dbo = JFactory::getDbo();
$columns = $dbo->getTableColumns('#__testtable', false);

if (array_key_exists('cid', $columns))
{
return;
}

$contents = file_get_contents($sqlFile);
$queries = $dbo->splitSql($contents);

foreach ($queries AS $query)
{
 $query = trim($query);

 if (empty($query))
 {
 continue;
 }

 $dbo->setQuery($query);
 $dbo->execute();



[Roger at Gmail]

Ace! Thank you, now I see how to do it.

[Mathew Lenning]

Glad to help =^D