Field 'order_site' doesn't have a default value

[webley1]

Recently had my ZC website hosting server crash. It finally came back online after a number of days and moving servers, updating dns etc. I've made no changes to the website, or any settings and it was supposed to be a full restore.


After it finally came back up I had a customer who mentioned an error on the site, at checkout. They click submit and it shows ("please reload the page and try again")", they go ahead and do that and it pulls the payment twice or as many times as they hit refresh. I've had another customer mention this same issue with I believe Paypal Direct API.

I found the following error in the logs.

Thanks for info. I see the following in there:
16-Jul-2017 02:57:26 UTC] PHP Fatal error: 1364:Field 'order_site' doesn't have a default value :: INSERT INTO zen_orders (customers_id, customers_name, customers_company, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, billing_name, billing_company, billing_street_address, billing_suburb, billing_city, billing_postcode, billing_state, billing_country, billing_address_format_id, payment_method, payment_module_code, shipping_method, shipping_module_code, coupon_code, cc_type, cc_owner, cc_number, cc_expires, date_purchased, orders_status, order_total, order_tax, currency, currency_value, ip_address, COWOA_order) (as called by) /home/xxxx/public_html/includes/functions/functions_general.php on line 965 <== in /home/xxxx/public_html/includes/classes/db/mysql/query_factory.php on line 155

I had another customer who tried to checkout with Paypal Express and they received the same error. This time I was sent an error log from Paypal.

"Dear store owner,
An error occurred when attempting to initiate a PayPal Express Checkout transaction. As a courtesy, only the error "number" was shown to your customer. The details of the error are shown below.

10415
Transaction refused because of an invalid argument. See additional error messages for details.
A successful transaction has already been completed for this token.

Zen Cart message: It appears that your order was submitted twice. Please check the My Account area to see the actual order details. Please use the Contact Us form if your order does not appear here but is already paid from your PayPal account so that we may check our records and reconcile this with you."

I believe that it may be related to sql or PHP upgrade that the host did on the new server, but am sure on that.

Thanks for your time and help.

[DrByte]

There is no field called "order_site" in the official Zen Cart code.

Thus, it has probably come from a plugin you've installed.

The "doesn't have a default value" error occurs when you create a "NOT NULL" field in the database without also specifying a "DEFAULT" value, and then you go and do an "INSERT" statement that doesn't supply any actual value for that new field in the inserted data.

Older versions of MySQL may have ignored the error, but modern versions enforce more strictness, which probably explains why you're suddenly seeing the error after having your site moved to a new server (that's running newer core software).

The "correct" fix is to either:
a) update the database schema to provide a "default" value (even if it's "blank");
or
b) update the PHP code that inserts the data, to include a value for that new field.


I suspect maybe you toyed with a plugin that added the field, and left that field in your database, but opted to skip the use of the PHP code that actually saves data into that field. In that case, if the "order_site" field is not actually being used at all in your site, you could delete the field from the "orders" (in your case "zen_orders") table. This will remove all associated data stored in that field in the past.

Or, if you want to keep the field, then you'll need to update its requirements to allow it to store an empty value if no value is supplied when inserting data into it.


This is not a bug in Zen Cart, so I'm moving this post out of the Bug Reports section.

[Design75]

It has to do with a mysql upgrade. Mysql gets more strict like PHP. The field is from the
multi site module.
Use phpmyadmin to go to your database. Find the orders table and view the structure. Edit the order_site field to have a default value of 1.

[webley1]

I really appreciate the replies. I've been pulling my hair out over this for a week or more. Modules have been installed for 5 plus years, with no changes to the site/module structure. Many of these were installed before I started running it as well.

I forgot to mention that when an order is placed it does not go under "new orders" although I can see the correct customer, under new customers.

I suppose this error is the one causing the multiple order issue as well?

[DrByte]

Having a "fatal error" halts PHP execution, so nothing happens in the logic after that error occurs.

So, it "might" fix the "multiple order issue" you mention. It might not. But if you don't fix the fatal error first, you won't know whether it affects other things directly or not. They might be completely unrelated. Keep an eye on all the new files generated in the /logs/ folder. There shouldn't be any myDebug-xxxxxx.log files generated if your site is operating properly and is fully compatible with the server software.

Should probably also upgrade to the latest Zen Cart, since your older version was written before some of the newer PHP and MySQL versions were even conceived.

[mc12345678]

I really appreciate the replies. I've been pulling my hair out over this for a week or more. Modules have been installed for 5 plus years, with no changes to the site/module structure. Many of these were installed before I started running it as well.

I forgot to mention that when an order is placed it does not go under "new orders" although I can see the correct customer, under new customers.

I suppose this error is the one causing the multiple order issue as well?

More than likely at least during this period of the customer not progressing through. Haven't looked at the sequence of code, but more than likely the "new" purchase history is processed after/in the sql error and doesn't get captured.

You say that you see the new customer records and payment has been received/processed, but what about existing customers and their purchases, have you figured out how to sort that out?

[webley1]

Thanks for the replies everyone. It appears that adding a 1 to the order_site field, in Zen Orders table has fixed the issue.

I appreciate all the time and help.