Problem with database upgrade v1.3.7 to 1.3.8

When trying to upgrade from 1.3.7.1 to 1.3.8a i get the following error when the database upgrade from 1.3.7 to 1.3.8 is chosen and i press "Update database now" in the Database Upgrade setup page.

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');]*

How can i fix this? And yes, i have made backups ;) :flex:

When i go back again after this error the page reports no upgrade is needed:
*Database Information -- Upgrade Sniffer predicts: *** No upgrade required ****

That entry looks to be for the newsletter subscribe add on and I do not know if they have a new sql for this or if one is needed

Does it still work?

it don't seem to work..

I get the following text when trying to access the shop:
ICON_WARNING_ALT WARNING_DATABASE_VERSION_OUT_OF_DATE

And i can't access the admin part at all..

I guess the update from 1.3.7 to 1.3.8 of the database stopped at that error message and did not complete..

kobra:

That entry looks to be for the newsletter subscribe add on and I do not know if they have a new sql for this or if one is neededSorry, that's not correct.
The query is a new record added as part of v1.3.8.

taz79:

When trying to upgrade from 1.3.7.1 to 1.3.8a i get the following error when the database upgrade from 1.3.7 to 1.3.8 is chosen and i press "Update database now" in the Database Upgrade setup page.

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');]*

How can i fix this? And yes, i have made backups ;) :flex:Edit /zc_install/sql/mysql_upgrade_zencart_137_to_138.sql
Find this line:```
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');

Replace it with this:```
INSERT INTO query_builder (query_category , query_name , query_description , query_string , query_keys_list ) 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', '');

Then run your 137-to-138 upgrade step again.

Hi guys....

My 1.37->1.38a upgrade went flawlessly, however, I did notice on any email function in admin, ie; tools >send email or tools > export email addrs, took a HUGE amount of time to return the page (1+min or so) I have something close to 6000 accounts in the DB. I have narrowed it down to this new query line added in the 1.3.8a upgrade. When I remove it from the query_builder table, everything is wonderfull, put back in the suggested change Dr. Byte left here, and back to slow as a slug...........

Since I don't use either of these, and makes littel difference to me (for now) if I have this query in my table, any harm in leaving it out? or ??

Zen 1.38a
PHP 5.25
MySql 5.0.45

Thanks........

jerkynet:

Hi guys....

My 1.37->1.38a upgrade went flawlessly, however, I did notice on any email function in admin, ie; tools >send email or tools > export email addrs, took a HUGE amount of time to return the page (1+min or so) I have something close to 6000 accounts in the DB. I have narrowed it down to this new query line added in the 1.3.8a upgrade. When I remove it from the query_builder table, everything is wonderfull, put back in the suggested change Dr. Byte left here, and back to slow as a slug...........

Since I don't use either of these, and makes littel difference to me (for now) if I have this query in my table, any harm in leaving it out? or ?If you edit the "query_category" field in that record of the database, and change 'email,newsletters' to just 'newsletters', it will only show up when composing a newsletter, instead of when sending emails.

DrByte:

If you edit the "query_category" field in that record of the database, and change 'email,newsletters' to just 'newsletters', it will only show up when composing a newsletter, instead of when sending emails.

Let me be clear on where I am.....

I have replaced the upgrade.sql with the suggestion above in this thread. I now have tried this with your suggestion above, this was with both the variations of the sql above. As expected, pull email and that settles the problem for the admin->send emails. Leaves the problem on the export email addresses (only one I use).

Bottom line to simplify my issue, is this query important to future upgrades, or can I just run without it? Without it, gets me doing what I need, but was concerned simply pulling this from the query_builder table, might cause an issue on future upgrades......

Thanks!
Dave

If it's a pain in your side, drop it.
Upgrades won't hinge on it.

Dropped...........after thinking this out, likely this this hinged to your "export emial addr" mod? Was just cruising that one to see if anything glared at me, but in the event I was unclear earlier, that mod is in play here.

Thanks!
Dave

DrByte:

If it's a pain in your side, drop it.
Upgrades won't hinge on it.

DrByte:

Edit /zc_install/sql/mysql_upgrade_zencart_137_to_138.sql
Find this line:```
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');

> Replace it with this:```
INSERT INTO query_builder (query_category , query_name , query_description , query_string , query_keys_list ) 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', '');

Then run your 137-to-138 upgrade step again.

Thanks! I will try this tomorrow night :) Hope it works :D Thx for support!!!

DrByte:

Edit /zc_install/sql/mysql_upgrade_zencart_137_to_138.sql
Find this line:```
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');

> Replace it with this:```
INSERT INTO query_builder (query_category , query_name , query_description , query_string , query_keys_list ) 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', '');

Then run your 137-to-138 upgrade step again.

Ok, i did as you said.. What does this change mean? Will it make any difference in the update?

I got the following message now..

Message at top of page:
NOTE: Skipped upgrade statements: 1
See details at bottom of page for your inspection.
(Details also logged in the "upgrade_exceptions" table.)

Message at bottom of page in red:
SKIPPED: Cannot ALTER or INSERT/REPLACE into table linkpoint_api because it does not exist. CHECK PREFIXES!

It seems to work now though.. I just have a lot of changes to put back and a lot of pictures to put in.. And to restore the old theme..

Thanks for the help! :)

That alert is informational only, and if you're not using the linkpoint payment module, the message is to be expected.

DrByte:

That alert is informational only, and if you're not using the linkpoint payment module, the message is to be expected.

Ok, thanks! So why the change in the SQL? What does that change do? Was this a bug in the update?

Sheesh. No, it's not a bug.

The update attempts to update all tables that need updates.
In your case, since you don't have the linkpoint payment module enabled, you don't have the linkpoint_api table in your database. Thus, when it checked to see if it could do the update that that table would need, it found it didn't exist, and recorded an alert about it.

I think in the future I'm going to suppress all upgrade exceptions ... since nobody seems to read the explanation text which specifically says that the messages are for information only, and may not necessarily indicate a serious problem. That's why they're called alerts, and not errors.