MySQL install script doesn't run?

Question

I am trying to make a custom module in magento 1.8.1 and I am following the tutorial given here

I found everything fantastic except that in this tutorial, the author is creating the table directly in the database, so if I move this module to another setup I have to create the table manually in the database, which I don't want to do. And also there is no entry for the module in the core_resource and core_config_data tables.

Now when I am trying to create the installer file in the sql folder of my custom module it is not running and shows an error

a:5:{i:0;s:102:"SQLSTATE[42S02]: Base table or view not found: 1146 Table'demo_magento_dummy.pfay_test' doesn't exist";i:1;s:4361:"#0 /opt/lampp/htdocs/magento_new/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /opt/lampp/htdocs/magento_new/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /opt/lampp/htdocs/magento_new/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /opt/lampp/htdocs/magento_new/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `main_ta...', Array)
#4 /opt/lampp/htdocs/magento_new/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `main_ta...', Array)
#5 /opt/lampp/htdocs/magento_new/lib/Zend/Db/Adapter/Abstract.php(734): Varien_Db_Adapter_Pdo_Mysql->query('SELECT `main_ta...', Array)
#6 /opt/lampp/htdocs/magento_new/lib/Varien/Data/Collection/Db.php(734): Zend_Db_Adapter_Abstract->fetchAll('SELECT `main_ta...', Array)
#7 /opt/lampp/htdocs/magento_new/app/code/core/Mage/Core/Model/Resource/Db/Collection/Abstract.php(521): Varien_Data_Collection_Db->_fetchAll('SELECT `main_ta...', Array)

and here is my mysql4-install-0.1.0.php

<?php
$installer = $this;
$installer->startSetup();
$installer->run("
DROP TABLE IF EXISTS pfay_test;
CREATE TABLE `pfay_test` (
`id_pfay_test` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `nom` VARCHAR( 50 ) NOT NULL ,
 `prenom` VARCHAR( 50 ) NOT NULL ,
 `telephone` VARCHAR( 20 ) NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");
$installer->endSetup();

I checked the version of the module to see if it is the problem of version but version are same and I am not able to understand what is the problem here.

So here is my config.xml

<?xml version="1.0"?>
<config>
 <modules>
 <Pfay_Test>
 <version>0.1.0</version>
 </Pfay_Test>
 </modules>
 <frontend>
 <routers>
 <routeurfrontend>
 <use>standard</use>
 <args>
 <module>Pfay_Test</module>
 <frontName>test</frontName>
 </args>
 </routeurfrontend>
 </routers>
 <layout>
 <updates>
 <test>
 <file>test.xml</file>
 </test>
 </updates>
 </layout>
</frontend>
<admin>
 <routers>
 <test>
 <use>admin</use>
 <args>
 <module>Pfay_Test</module>
 <frontName>admintest</frontName>
 </args>
 </test>
 </routers> 
</admin>
<adminhtml>
 <layout>
 <updates>
 <test>
 <file>test.xml</file>
 </test>
 </updates>
 </layout>
 <menu>
 <test translate="title" module="adminhtml">
 <title>My Plugins</title>
 <sort_order>100</sort_order>
 <children>
 <set_time>
 <title>Address Book</title>
 <action>admintest/adminhtml_index</action>
 </set_time>
 </children>
 </test>
 </menu>
</adminhtml>
<global>
 <blocks>
 <test>
 <class>Pfay_Test_Block</class>
 </test>
 </blocks>
 <models>
 <test>
 <class>Pfay_Test_Model</class>
 <resourceModel>test_mysql4</resourceModel> 
 </test>
 <test_mysql4>
 <class>Pfay_Test_Model_Mysql4</class>
 <entities>
 <test>
 <table>pfay_test</table>
 </test>
 </entities>
 </test_mysql4>
 </models>
<!-- allow the plugin to read and write -->
 <resources>
 <test_setup>
 <setup>
 <module>Pfay_Test</module>
 </setup>
 <connection>
 <use>core_setup</use>
 </connection>
 </test_setup> 
<!-- connection to write -->
 <test_write>
 <connection>
 <use>core_write</use>
 </connection>
 </test_write>
<!-- connection to read -->
 <test_read>
 <connection>
 <use>core_read</use>
 </connection> 
 </test_read>
 </resources>
</global>

• Evgeni Ivanov: Do not hardcode the table name. Maybe you tables are using some prefix. Always use $installer->getTable(). Read more here : codegento.com/2011/02/install-scripts-and-upgrade-scripts and here magentocommerce.com/knowledge-base/entry/…
• Sukeshini: Can you post your module's config.xml here?
• Marius: can you post the config.xml of your module and the path to the install script?
• Waseem: So any solution now, I really need a help with this
• Marius: The config looks ok. Make sure you placed the install script in sql/test_setup/install-0.1.0.php

Answer 1

<?php
$installer = $this;
$installer->startSetup();
$installer->run("
DROP TABLE IF EXISTS `{$installer->getTable('test/test')}`;
CREATE TABLE `{$installer->getTable('test/test')}` (
`id_pfay_test` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `nom` VARCHAR( 50 ) NOT NULL ,
 `prenom` VARCHAR( 50 ) NOT NULL ,
 `telephone` VARCHAR( 20 ) NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");
$installer->endSetup();

Answer 2

Following are the possible reasons for not running your module sql script

1. Open the database and check in core_resource table, Find your module setup record like "customlink_setup" and delete that record if you found and try again.
2. Open the database and check in eav_attribute table, Find your attribute ID like "thumbnail_link" in table and delete it if you found.
3. Check your module version in config.xml file and mysql installer file version number must be same

I Hope It will help you

Code taken from this link http://chandreshrana.blogspot.in/2015/09/magento-my-module-extension-sql.html