1364 Field 'oc_note' doesn't have a default value

[VirtueMocer]

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]

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.

[pinochico]

Duplicate post:
http://forum.virtuemart.net/index.php?topic=146112.0

[VirtueMocer]

@ 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]

Check that the Sisow plugin is compatible with VM3.8.6 

[VirtueMocer]

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]

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

[VirtueMocer]

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]

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.

[GJC Web Design]

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

[VirtueMocer]

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]

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'

[VirtueMocer]

Sorry GJC,

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

[GJC Web Design]

I would have said unlikely -- it certainly can't do any harm ..

[VirtueMocer]

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?