1. It thinks I need to do an upgrade from 1.2.7->1.2.8 as well as from 1.3.7->1.3.8 And during the 1.3.8 upgrade I get this error.

Thanks !!!

1364 Field 'query_keys_list' doesn't have a default value
in:
[INSERT INTO query_builder (query_category, query_name, query_description , query_string) VALUES ('email,newsletters', 'Customers who have never completed a purchase', 'For sending newsletter to all customers who registered but have never completed a purchase', 'SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM TABLE_CUSTOMERS c LEFT JOIN TABLE_ORDERS o ON c.customers_id=o.customers_id WHERE o.date_purchased IS NULL');]

quentinjs:

1. It thinks I need to do an upgrade from 1.2.7->1.2.8 as well as from 1.3.7->1.3.8Sorry, I'm not sure I understand you.
   There is no such thing as v1.2.8

Hi you gods of zen...:cool:

I have a small issue with the database update... i did merge everything successfulley data wise and then - as i clicked "update database from 1.3.7.1 to 1.3.8 in the install i received these errors:

SKIPPED: Cannot ADD column module_name because it already exists.
SKIPPED: Cannot ADD column module_mode because it already exists.
SKIPPED: Cannot CHANGE column zen_order_id because it does not exist.
SKIPPED: Cannot ALTER or INSERT/REPLACE into table linkpoint_api because it does not exist. CHECK PREFIXES!

so... i take that the first 2 are ok but i'm worried about the last 2 errors...

Those "warnings" are informational, and suggest that some of the steps in the upgrade had already been done previously.
Thus, you should be safe to proceed.

Also note that the last warning is specifically listed as "okay" in the 1.3.8 announcement

So lets start this again.. I go in, say upgrade from 1.3.7. to 1.3.8 and get this error message...

1364 Field 'query_keys_list' doesn't have a default value
in:
[INSERT INTO query_builder (query_category, query_name, query_description , query_string) VALUES ('email,newsletters', 'Customers who have never completed a purchase', 'For sending newsletter to all customers who registered but have never completed a purchase', 'SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM TABLE_CUSTOMERS c LEFT JOIN TABLE_ORDERS o ON c.customers_id=o.customers_id WHERE o.date_purchased IS NULL');]

thoughts?

That error appears because that step has already been done once.

Follow the steps in this thread (which I found by searching for "Customers who have never completed") to delete that entry so you can start over again.
http://www.zen-cart.com/forum/showthread.php?t=82998&highlight=Customers+who+have+never+completed

EDIT: Oops: I misread the error message, and recommended the wrong solution.

Thanks! That should I hope help a lot.

Okay it didn't help, which I think is due to the database being PHP5 / MySQL5. Any more thoughts?

MySQL Version = 5.0.37

PHP Version = 5.2.1

Actually I don't think the record is there. I know from past experiences with upgrades that if an insert is missing a field in the list then it wants a default value. Now part of the error message mentioned:

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

So I wonder what the default value should be?

You can bypass the whole insert ... but this is the most-unrecommended-way possible:

open /zc_install/sql/mysql_upgrade_zencart_137_to_138.sql
find line 61:```
#audience list addition: customers who have signed up but never made a purchase
INSERT INTO query_builder (query_category, query_name, query_description , query_string) VALUES ('email,newsletters', 'Customers who have never completed a purchase', 'For sending newsletter to all customers who registered but have never completed a purchase', 'SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM TABLE_CUSTOMERS c LEFT JOIN TABLE_ORDERS o ON c.customers_id=o.customers_id WHERE o.date_purchased IS NULL');

Put a # symbol at the beginning of that INSERT line, and save the file.
DO NOT MAKE ANY OTHER CHANGES.

Then do your 1.3.7-to-1.3.8 upgrade step again.

For future readers:
**IT IS NOT RECOMMENDED TO EVER EDIT THE SQL FILES IN THE ZC_INSTALL FOLDER**

Try running this to set the query_keys_list field to its correct format:

ALTER TABLE query_builder CHANGE COLUMN query_keys_list query_keys_list TEXT NOT NULL;

It comes from an upgrade step in the upgrade to 1.3.0.2 and 1.3.5 where that field was changed. Perhaps yours didn't get that step done properly for some reason.

Actually I don't think the record is there. I know from past experiences with upgrades that if an insert is missing a field in the list then it wants a default value. Now part of the error message mentioned:

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

So I wonder what the default value should be?

And I thought you'd be sleeping! Glad your not.... scratch the above, I think you just sent the answer.

quentinjs:

Actually I don't think the record is there. I know from past experiences with upgrades that if an insert is missing a field in the list then it wants a default value. Now part of the error message mentioned:

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

So I wonder what the default value should be?You're correct; however, a field of type "TEXT" never gets a default value. Thus, your table must have the old structure which used varchar ... way back in 1.2.something.

From the MySQL documentation:

BLOB and TEXT columns cannot have DEFAULT values.

Okay, still not working, should it be NULL or NOT NULL ? if NOT NULL, then the insert needs to specify a value, will NULL work?

I suppose you can try NULL.

I'm using MySQL 5.0.45 and PHP 5.2.4 and have never run into this problem.
I would never have released the code if this was manifesting as a bug.
I'm puzzled about why you're the only one reporting this particular issue.

I'm sure I am special...

I know that mysql 5 has complained in the past when an insert has a not null field, and that field isn't specified within the insert.

It succeeded!

Now I'm posting the list of exceptions as a sanity check, the paypal_testing does make me wonder a little....

SKIPPED: Cannot insert configuration_key "SHOW_SHOPPING_CART_COMBINED" because it already exists
SKIPPED: Cannot insert configuration_key "ORDER_COMMENTS_INVOICE" because it already exists
SKIPPED: Cannot insert configuration_key "ORDER_COMMENTS_PACKING_SLIP" because it already exists
SKIPPED: Cannot insert configuration_key "ORDER_COMMENTS_PACKING_SLIP" because it already exists
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table paypal_testing because it does not exist. CHECK PREFIXES!
SKIPPED: Cannot ALTER or INSERT/REPLACE into table linkpoint_api because it does not exist. CHECK PREFIXES!

paypal_testing is only for private development use - you can ignore it.

the others are safe to ignore as well

So ... what is the structure of your query_builder table at this point?

Oh, and my apologies for missing the actual wording of your specific error message the first two times around. There have been several reports of duplicate records on that same SQL statement, and I just jumped to that automatically ... oops.

query_id integer not null auto inc
_category varchar(40) not null
_name varchar(80) not null
_description TEXT NOT NULL
_string TEXT NOT NULL
_keys_list all TEXT NULL

quentinjs:

query_id integer not null auto inc
_category varchar(40) not null
_name varchar(80) not null
_description TEXT NOT NULL
_string TEXT NOT NULL
_keys_list **all **TEXT NULL

"all" ?