Author Topic: 1364 Field 'oc_note' doesn't have a default value  (Read 595 times)

VirtueMocer

  • Beginner
  • *
  • Posts: 9
  • A beginner
1364 Field 'oc_note' doesn't have a default value
« on: January 08, 2021, 09:41:45 am »
Hi there,

I'm new to Virtuemart and ran into a problem after moving a Virtuemart installation to another hoster. Trying to send and place an order results in the message: 1364 Field 'oc_note' doesn't have a default value. No order will be logged into the system. Anyone knows what the problem can be?

Thanks in advance!
Mocer

jenkinhill

  • UK Web Developer & Consultant
  • Global Moderator
  • Super Hero
  • *
  • Posts: 28016
  • Always on vacation
    • Jenkin Hill Internet
Re: 1364 Field 'oc_note' doesn't have a default value
« Reply #1 on: January 08, 2021, 12:03:59 pm »
Versions?  How is the new host different?

How did you move the site?  I have moved hundreds of sites using Akeeba and never had an issue.
Kelvyn

Jenkin Hill Internet,
Lowestoft, Suffolk, UK

Unsolicited PMs/emails will be ignored.

Please mention your VirtueMart, Joomla and PHP versions when asking a question in this forum

Currently using VirtueMart 3.8.7 10374 on Joomla 3.9.24 PHP 7.4.12

pinochico

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 901
    • MiniJoomla
  • Skype Name: support-easysoftware
  • VirtueMart Version: 3
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds from products, orders and database table
Virtuemart Email Manager - customs email templates

VirtueMocer

  • Beginner
  • *
  • Posts: 9
  • A beginner
Re: 1364 Field 'oc_note' doesn't have a default value
« Reply #3 on: January 08, 2021, 13:43:59 pm »
@ Kelvyn: I used Akeeba backup. The php version of the new host was set to 7.3 instead of 7.4, because there was a deprecated error of the sisow payment extension. VirtueMart 3.8.6 10373. The rest of the website seems to work fine!

@ pinochico: This is another question compared with http://forum.virtuemart.net/index.php?topic=146112.0

Cheers,
Mocer

jenkinhill

  • UK Web Developer & Consultant
  • Global Moderator
  • Super Hero
  • *
  • Posts: 28016
  • Always on vacation
    • Jenkin Hill Internet
Re: 1364 Field 'oc_note' doesn't have a default value
« Reply #4 on: January 08, 2021, 16:51:56 pm »
Check that the Sisow plugin is compatible with VM3.8.6 
Kelvyn

Jenkin Hill Internet,
Lowestoft, Suffolk, UK

Unsolicited PMs/emails will be ignored.

Please mention your VirtueMart, Joomla and PHP versions when asking a question in this forum

Currently using VirtueMart 3.8.7 10374 on Joomla 3.9.24 PHP 7.4.12

VirtueMocer

  • Beginner
  • *
  • Posts: 9
  • A beginner
Re: 1364 Field 'oc_note' doesn't have a default value
« Reply #5 on: January 09, 2021, 15:05:04 pm »
Hi Kelvin,

After deactivating the Sisow plug-in the deprecation error is gone, but the error "1364 Field 'oc_note' doesn't have a default value" still remains after trying to place an order. Is it possible that the Orcer Notice not been shown has a relation with changing Safe path setup?

Thanks in advance1

Mocer

GJC Web Design

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 10027
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 3.6.8
Re: 1364 Field 'oc_note' doesn't have a default value
« Reply #6 on: January 09, 2021, 16:22:46 pm »
the only reference I can find to oc_note is in the install script

$this->alterTable('#__virtuemart_orders',array(
         'customer_note' => '`oc_note` text NOT NULL DEFAULT "" COMMENT \'old customer notes\'',
      ));

which is change "customer_note" to "oc_note" in the #__virtuemart_orders table to preserve old notes  when customer notes was moved to #__virtuemart_userinfos

and a fallback in the oders display

         //Fallback for customer_note
         if(empty($orderbt->customer_note) and !empty($orderbt->oc_note)){
            $orderbt->customer_note = $orderbt->oc_note;
         }

Try running the tables updater in VM config tools ->  Install or if necessary update tables

GJC Web Design
VirtueMart and Joomla Developers - php developers http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

VirtueMocer

  • Beginner
  • *
  • Posts: 9
  • A beginner
Re: 1364 Field 'oc_note' doesn't have a default value
« Reply #7 on: January 09, 2021, 17:05:13 pm »
Hi GJC,

Unfortunately running the tables updater in VM config tools didn't do the trick. Still not able to place an order. Is it possible (and wise?) to re-install VM while keeping all data?

jenkinhill

  • UK Web Developer & Consultant
  • Global Moderator
  • Super Hero
  • *
  • Posts: 28016
  • Always on vacation
    • Jenkin Hill Internet
Re: 1364 Field 'oc_note' doesn't have a default value
« Reply #8 on: January 09, 2021, 20:35:02 pm »
You can certainly install Virtuemart on top of an existing installation. Grab the relevant package from http://dev.virtuemart.net/projects/virtuemart/files  and install what you need using Joomla's Extensions/Manage/Install.

Do not try to uninstall the old version! In any case, you should be trying any fix on a backup copy of the live site.
Kelvyn

Jenkin Hill Internet,
Lowestoft, Suffolk, UK

Unsolicited PMs/emails will be ignored.

Please mention your VirtueMart, Joomla and PHP versions when asking a question in this forum

Currently using VirtueMart 3.8.7 10374 on Joomla 3.9.24 PHP 7.4.12

GJC Web Design

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 10027
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 3.6.8
Re: 1364 Field 'oc_note' doesn't have a default value
« Reply #9 on: January 09, 2021, 21:02:18 pm »
the error is probably caused by MySQL having a strict mode set which won't allow INSERT or UPDATE commands with empty fields where the schema doesn't have a default value set.
This may be the difference between the old and new host

so u could also try setting a default value for #__virtuemart_orders.oc_note

something like  ALTER TABLE `PREFIX_virtuemart_orders` CHANGE COLUMN `oc_note` `oc_note` varchar(5000) NOT NULL DEFAULT '' ;

or DEFAULT 'x'

or see if the host will reset strict mode

or try to run

SET GLOBAL sql_mode='';

and see if it makes a difference
GJC Web Design
VirtueMart and Joomla Developers - php developers http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

VirtueMocer

  • Beginner
  • *
  • Posts: 9
  • A beginner
Re: 1364 Field 'oc_note' doesn't have a default value
« Reply #10 on: January 11, 2021, 10:29:15 am »
Unfortunately MySQL having a strict mode set and the hoster isn't able to turn it off at the shared hosting environment. Any other options to tackle this problem?

GJC Web Design

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 10027
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 3.6.8
Re: 1364 Field 'oc_note' doesn't have a default value
« Reply #11 on: January 11, 2021, 10:38:49 am »
???

Quote
so u could also try setting a default value for #__virtuemart_orders.oc_note

something like  ALTER TABLE `PREFIX_virtuemart_orders` CHANGE COLUMN `oc_note` `oc_note` varchar(5000) NOT NULL DEFAULT '' ;

or DEFAULT 'x'
GJC Web Design
VirtueMart and Joomla Developers - php developers http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

VirtueMocer

  • Beginner
  • *
  • Posts: 9
  • A beginner
Re: 1364 Field 'oc_note' doesn't have a default value
« Reply #12 on: January 11, 2021, 11:11:10 am »
Sorry GJC,

I'm not experienced with PHP/MySQL  ;). Changing this column doesn't affect upcoming VM updates?

GJC Web Design

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 10027
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 3.6.8
Re: 1364 Field 'oc_note' doesn't have a default value
« Reply #13 on: January 11, 2021, 12:04:38 pm »
I would have said unlikely -- it certainly can't do any harm ..
GJC Web Design
VirtueMart and Joomla Developers - php developers http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

VirtueMocer

  • Beginner
  • *
  • Posts: 9
  • A beginner
Re: 1364 Field 'oc_note' doesn't have a default value
« Reply #14 on: January 12, 2021, 10:46:34 am »
Hi GJC,

After running ALTER TABLE `PREFIX_virtuemart_orders` CHANGE COLUMN `oc_note` `oc_note` varchar(5000) NOT NULL DEFAULT '' ; I got another error trying to place an order: 1292 Incorrect datetime value: '0' for column `DATABASE_NAME`.`#__virtuemart_orders`.`paid_on` at row 1.
Any suggestions?