Joomla module install.sql not executing

Question

I have the following file install.sql in my site module's /sql folder. And I'm referencing it from my module's .xml file like so:

<?xml version="1.0" encoding="utf-8"?>
<extension type="module" version="2.5" client="site" method="install">
    <name>bbb Referral</name>
    <author>AAA Media</author>
    <creationDate>23/02/2012</creationDate>
    <copyright>Copyright (C) 2005 - 2012 Open Source Matters. All rights reserved.</copyright>
    <license>GNU General Public License version 2 or later</license>
    <authorEmail>info@AAA</authorEmail>
    <authorUrl>AAA</authorUrl>
    <version>1.0</version>
    <description>bbb Referral</description>
    <files>
        <filename module="mod_bbb_referral">mod_bbb_referral.php</filename>
        <folder>tmpl</folder>
        <filename>index.html</filename>                       
        <filename>mod_bbb_referral.xml</filename>
    </files>
    <install folder="site">
        <sql>
            <file driver="mysql" charset="utf8">sql/install.sql</file>
        </sql>
    </install>
    <languages folder="language/en-GB">
        <language tag="en-GB">en-GB.ini</language>
    </languages>
    <config>
        <fields name="params">
            <fieldset name="basic">
                <field name="header_text" type="textarea" default="" label="Header text" description="Header text to show before friends list" />
                <field name="footer_text" type="textarea" default="" label="Footer text" description="Footer text to show below friends list" />
                <field name="body_text" type="textarea" default="" label="Body text"
                       description="Body text to include in referral" />
                <field name="thankyou_text" type="textarea" default="" label="Thank you text"
                       description="Thank you text to show when successful" />
                <field name="error_text" type="textarea" default="Error encountered. Please try again later." label="Error text"
                       description="Error text to show if share was not successful in any way." />
                <field name="socialgraph_url" type="text" default="" size="60" label="Social Graph request URL"
                       description="URL to access when getting user's friend list, leave {filter}, {skip} and {count} in URL, they will be replaced upon run-time." />
                <field name="socialgraph_filter" type="text" default="@Friends" label="Social Graph list filter"
                       description="Filter to use when getting list." />
                <field name="socialgraph_page_count" type="text" default="" label="Friends per page" description="Default is 26, leave empty to use default" />
                <field name="socialgraph_oauth_scope" type="text" default="graph/read" label="OAuth scope" description="Separated by a {space}. DO NOT CHANGE if you're unsure." />
            </fieldset>
        </fields>
    </config>
</extension>

But it specifically does not execute the create part of the table contained in /sql:

CREATE TABLE IF NOT EXISTS `#__oauth_api_log` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `tracking_session_id` int(11) unsigned NOT NULL,
  `type` char(20) default '',
  `api_request` mediumtext,
  `api_response` mediumtext,
  `api_result` mediumtext,
  `execution_time` float default '-1',
  `date_time` timestamp NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
);

This table is needed for logging.

Any help/pointers are appreciated.

Answer 1

I spent many hours on this problem. I found a solution :

• put the sql files in the root of the module

• declare them in the files tag:

  (filename)install.mysql.utf8.sql(/filenam)
  (filename)uninstall.mysql.utf8.sql(/filename)
  

and add the instal/uninstall tags:

(install)
    (sql)
        (file charset="utf8" driver="mysql")install.mysql.utf8.sql(/file)
    (/sql)
(/install)

(uninstall)
    (sql)
        (file charset="utf8" driver="mysql")uninstall.mysql.utf8.sql(/file)
    (/sql)
(/uninstall)

Not very clean, but I don't want to waste more time searching for the right path

Answer 2

I can think of a few possible issues.

1. You're not uninstalling the module correctly between attempts at installing, so the install SQL is not being run.

2. The SQL file is not saved as UTF8 NO BOM (although this used to cause an error #1064 to be thrown)

3. The path is not being calculated correctly (again, this would normally throw an error)

4. You may have an SQL bug, you say "it specifically does not execute the create part of the table", does this mean there is other SQL in the file that you're not showing that could have an error?

Suggestions

If you're not seeing any errors I would suggest you make sure that in your Global Configuration->Server you have Error Reporting set to Development and in Global Configuration->System you have Debug System set to Yes.

Development Error Reporting — will set the value in the configuration.php file to

public $error_reporting = 'development';

and result in you seeing every error PHP finds.

NB. sometimes if your errors are bad enough you won't be able to access your site, in this case edit the configuration.php and revert to $error_reporting = 'none'

Debug System — will set the value in the configuration.php file to

public $debug = '1';

and will add the Joomla! Debug Console to the bottom of each page:

NB. The section called Database Queries will show any SQL logged while the site is in debug mode, you can then check through them to see what's happening... i.e. is your SQL being run or not...

If none of this helps but you get any additional details like errors, add them to your question and I'll try and help further.

Answer 3

If you look at your XML, you will see the following:

<install folder="site">

This means that it is looking for:

site/sql/install.sql

So simply, remove folder="site"

Hope this helps