Syntax error or access violation: 1067 Invalid default value for 'period'

If you have this error message when installing Magento 1.6: Error in file: "app\code\core\Mage\SalesRule\sql\salesrule_setup\upgrade-1.6.0.0-1.6.0.1.php" - SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'period', here is the article that will help you solve it.

I got this error message lots of time when installing Magento 1.6 in localhost. However, I do not get it when installing in online server. After struggling time, I find the reason is for sql file of SaleRule module.

To avoid the error, before installing, you need to edit the file install-1.6.0.0.php in app\code\core\Mage\SalesRule\sql\salesrule_setup. First, in the line 333, change from

->addColumn('period', Varien_Db_Ddl_Table::TYPE_DATE, null, array(
'nullable' => false,
), 'Period')

to

->addColumn('period', Varien_Db_Ddl_Table::TYPE_DATE, null, array(
'nullable' => false,
'default' => '0000-00-00',
), 'Period')

Then, you will need to change the code around line 393, from

->addColumn('period', Varien_Db_Ddl_Table::TYPE_DATE, null, array(
'nullable' => false,
), 'Period')

to

->addColumn('period', Varien_Db_Ddl_Table::TYPE_DATE, null, array(
'nullable' => false,
'default' => '0000-00-00',
), 'Period')

That's it. Now you can start installing. I hope this helps someone. If you have any question, please do not hesitate to contact me.

5 thoughts on “Syntax error or access violation: 1067 Invalid default value for 'period'”

5 Responses to Syntax error or access violation: 1067 Invalid default value for 'period'

  • Will
    Will on July 19, 2012 at 1:35 am said:

    Thank you for this Tutorial / bug fix!

  • Edgar
    Edgar on July 21, 2012 at 1:34 am said:

    Hello Neo, I am going to develop a web site destined to sales, but I donĀ“t know if Magento is better than other cms. What do you think about this?

  • Neo

    Hi Edgar,
    If you are willing to use the website for selling products, I suggest you to use Magento shopping cart. Magento is a good choice for shopping cart website. It also allows you to manage cms content.
    If you have any further question, you can email me at neo[at]magebuzz[dot]com

  • EZer0

    That is great! Saved my day! I wonder why this really stupid bug was not fixed in official community release. Thanks a lot, Neo!

  • Marco

    Thank you! I was stuck after installing / uninstalling Magento for the 3rd time, always ending up with the same error: your solution worked 100%.

    Note: I used your fix to install the latest version as of today, 1.7.0.2 ... brilliant!ffff by

Comments