Magento Question posted in
The official documentation can be found here.

Error Creating Custom table in Magento 1.9 for Custom Module

I’ve created a custom Magento plugin, and everything is working fine, except the database creation. Here is my SQL code.

<?php
$installer = $this;
$installer->startSetup();
$installer->run("
-- DROP TABLE IF EXISTS {$this->getTable('store_city')};
CREATE TABLE '{$installer->getTable('store_city')}'(
  `city_id` int(11) unsigned NOT NULL auto_increment,
  `city_name` text NOT NULL default '',
  `is_active` smallint(6) NOT NULL default '1',
  `sort_order` int(11) unsigned NOT NULL,
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS {$this->getTable('store_area')};
CREATE TABLE '{$installer->getTable('store_area')}'(
  `area_id` int(11) unsigned NOT NULL auto_increment,
  `area_name` text NOT NULL default '',
  `is_active` smallint(6) NOT NULL default '1',
  `sort_order` int(11) unsigned NOT NULL,
  `area_city_id` int(11) unsigned,
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`area_id`),
  FOREIGN KEY (`area_city_id`) REFERENCES {$this->getTable('store_city')} (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS {$this->getTable('store_store')};
CREATE TABLE '{$installer->getTable('store_store')}'(
  `store_id` int(11) unsigned NOT NULL auto_increment,
  `store_name` text NOT NULL default '',
  `store_address` text NOT NULL default '',
  `long` text,
  `lat` text,
  `is_active` smallint(6) NOT NULL default '1',
  `sort_order` int(11) unsigned NOT NULL,
  `store_area_id` int(11) unsigned,
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`store_id`),
  FOREIGN KEY (`store_area_id`) REFERENCES {$this->getTable('store_area')} (`area_id`)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");

$installer->endSetup();

but it gives me this error after I copy the plugin and test it, How can i fix this, and why is this happening?

a:5:{i:0;s:435:"Error in file: "Sites/magento/app/code/community/Test/Storelocator/sql/storelocator_setup/mysql4-install-0.1.0.php" - SQLSTATE[42000]: Syntax error or access violation: 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 ''store_city'(
  `city_id` int(11)  NOT NULL auto_increment,
  `c' at line 2";i:1;s:1124:"#0 Sites/magento/app/code/core/Mage/Core/Model/Resource/Setup.php(644): Mage::exception('Mage_Core', 'Error in file: ...')
#1 Sites/magento/app/code/core/Mage/Core/Model/Resource/Setup.php(421): Mage_Core_Model_Resource_Setup->_modifyResourceDb('install', '', '0.1.0')
#2 Sites/magento/app/code/core/Mage/Core/Model/Resource/Setup.php(327): Mage_Core_Model_Resource_Setup->_installResourceDb('0.1.0')
#3 Sites/magento/app/code/core/Mage/Core/Model/Resource/Setup.php(235): Mage_Core_Model_Resource_Setup->applyUpdates()
#4 Sites/magento/app/code/core/Mage/Core/Model/App.php(428): Mage_Core_Model_Resource_Setup::applyAllUpdates()
#5 Sites/magento/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Model_App->_initModules()
#6 Sites/magento/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#7 Sites/magento/index.php(83): Mage::run('', 'store')
#8 {main}";s:3:"url";s:24:"/magento/index.php/admin";s:11:"script_name";s:18:"/magento/index.php";s:4:"skin";s:7:"default";}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    There was some mistakes in the code, after finishing and now everything works, here is the code.

    <?php
$installer= $this;
$storeCity = $installer->getTable('store_city');
$storeArea = $installer->getTable('store_area');
$storeStore = $installer->getTable('store_store');

$installer->startSetup();
$installer->run("
DROP TABLE IF EXISTS $storeCity;
CREATE TABLE $storeCity(
`city_id` int(11) unsigned NOT NULL auto_increment,
`city_name` text NOT NULL,
`is_active` smallint(6) NOT NULL default '1',
`sort_order` int(11) unsigned NOT NULL,
`created_time` datetime NULL,
`update_time` datetime NULL,
PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS $storeArea;
CREATE TABLE $storeArea(
`area_id` int(11) unsigned NOT NULL auto_increment,
`area_name` text NOT NULL ,
`is_active` smallint(6) NOT NULL default '1',
`sort_order` int(11) unsigned NOT NULL,
`area_city_id` int(11) unsigned,
`created_time` datetime NULL,
`update_time` datetime NULL,
PRIMARY KEY (`area_id`),
FOREIGN KEY (`area_city_id`) REFERENCES store_city (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS  $storeStore;
CREATE TABLE  $storeStore(
`store_id` int(11) unsigned NOT NULL auto_increment,
`store_name` text NOT NULL ,
`store_address` text NOT NULL ,
`long` text,
`lat` text,
`is_active` smallint(6) NOT NULL default '1',
`sort_order` int(11) unsigned NOT NULL,
`store_area_id` int(11) unsigned,
`created_time` datetime NULL,
`update_time` datetime NULL,
 PRIMARY KEY (`store_id`),
 FOREIGN KEY (`store_area_id`) REFERENCES store_area (`area_id`)
 ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 ");

 $installer->endSetup();

  2. 0

    Try to remove the default value from “text”column, the text column can not have default value
    you can use this code 

    <?php
$installer = $this;
$installer->startSetup();
$installer->run("
-- DROP TABLE IF EXISTS {$this->getTable('store_city')};
CREATE TABLE '{$installer->getTable('store_city')}'(
  `city_id` int(11) unsigned NOT NULL auto_increment,
  `city_name` text NOT NULL,
  `is_active` smallint(6) NOT NULL default '1',
  `sort_order` int(11) unsigned NOT NULL,
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS {$this->getTable('store_area')};
CREATE TABLE '{$installer->getTable('store_area')}'(
  `area_id` int(11) unsigned NOT NULL auto_increment,
  `area_name` text NOT NULL ,
  `is_active` smallint(6) NOT NULL default '1',
  `sort_order` int(11) unsigned NOT NULL,
  `area_city_id` int(11) unsigned,
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`area_id`),
  FOREIGN KEY (`area_city_id`) REFERENCES {$this->getTable('store_city')} (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS {$this->getTable('store_store')};
CREATE TABLE '{$installer->getTable('store_store')}'(
  `store_id` int(11) unsigned NOT NULL auto_increment,
  `store_name` text NOT NULL ,
  `store_address` text NOT NULL ,
  `long` text,
  `lat` text,
  `is_active` smallint(6) NOT NULL default '1',
  `sort_order` int(11) unsigned NOT NULL,
  `store_area_id` int(11) unsigned,
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`store_id`),
  FOREIGN KEY (`store_area_id`) REFERENCES {$this->getTable('store_area')} (`area_id`)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");

$installer->endSetup();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search