Newsletter error TABLE_ORDERS doesn't exist

[buildingblocks]

Ok, I think I have had success.

I tried to run the upgrade routine but at first was receiving a warning and wouldn't process. A debug file said

[12-Dec-2014 14:41:59 America/Chicago] PHP Fatal error:  1062:Duplicate entry 'Customers who have never completed a purchase' for key 'query_name' :: INSERT INTO zen_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'); in /home2/user/public_html/mysite.com/truesol/includes/classes/db/mysql/query_factory.php on line 120

There wasn't a double entry so I just dropped the query_builder table. The database upgrade was then able to run. I let it upgrade starting from 1.3.7 to 1.3.8 and up to 1.5.1. It skipped a bunch of things but also ran a bunch of things. One thing it did not insert though was the query_builder table. So I went to another 1.5.1 database, exported a query_builder table and imported it into the database I am working with.

The email and newsletter functions are working again. Admin looks to be ok and the storefront is loading and looks ok.

Before I put the live site in maintenance mode and do this process one last time with a fresh database copy can you see any potential problems with what I have done?

[mc12345678]

So the query builder table I thought would have already been in the system design back in 1.3.7, so doing the upgrade (one time is all that is necessary to go from the older version to the current version), would not insert the table if it wasn't something newly added between that older version and the version currently being added. So dropping the table would not get it recreated. This goes back to the fact that there is/was an error in the content of the information within the table and that if that content issue was corrected then the upgrade would move quickly forward.

One issue with dropping the table is that obviously will lose any queries that may have been added independently of an installation. So would want to capture what those queries are before deleting the table. Secondly, though it may be of benefit to rename the table instead of deleting it entirely so that the data can be compared. (little suggestion offered by RodG in a recent post). If though the issue is to recreate a query and that query happens to be the same one that has been causing trouble, then really seems that something is not being performed correctly if the issue has persisted when trying to follow lat9's recommendation... It does seem like there is an extra "return" (CRLF) in the original content, so not sure how the replacement query was inserted, but it appears that it should be one continuous string, even though it will naturally wrap around to a new line in the text entry screen. The enter key should not be pressed as part of the entry in phpmyadmin.

[buildingblocks]

renaming the table instead of dropping it is a good idea. When doing the upgrade I wasn't sure if the query_builder table would be re-inserted but I didn't have anything to lose by trying it.

When doing the upgrade I checked all the boxes beginning with 1.3.7 to 1.3.8 and up to 1.5.1 and let it run in one proccess. I think if the upgrade had been done correctly the first time then when I did the re-upgrade all of the processes would have been skipped but many of them were processed. So I don't know if it was just the just the query_builder table that was the problem.


Yes there are extra returns in the code I had posted. That's just how it appears in the original database.

When I added the code that lat9 provided I copied and pasted it into the query_string field (so it was all one line) and saved it. Would that have been an ok way to add it?

[mc12345678]

renaming the table instead of dropping it is a good idea. When doing the upgrade I wasn't sure if the query_builder table would be re-inserted but I didn't have anything to lose by trying it.

When doing the upgrade I checked all the boxes beginning with 1.3.7 to 1.3.8 and up to 1.5.1 and let it run in one proccess. I think if the upgrade had been done correctly the first time then when I did the re-upgrade all of the processes would have been skipped but many of them were processed. So I don't know if it was just the just the query_builder table that was the problem.

When I added the code that lat9 provided I copied and pasted it into the query_string field (so it was all one line) and saved it. Would that have been an ok way to add it?

To ensure that it was all "one line" I would typically also go to the end of any row, remove the extra space(s) and then reinsert at least one space allowing the "text editor" to do with it what it may. Generally speaking I would say that it is an "ok" way to add it/modify it... Again the intent here would be to replace the existing query string with the new one as there is/was a problem with the old one.

Okay, wasn't sure from the previous description if each upgrade was painstakingly performed one at a time or all at once... There may also be value to the messages that were indicated, unless they all said something like skipped because already present or nothing to do because doesn't exist or some "informative" result like that.

As for the earlier description of doing the upgrade. There is no need to place the configure.php file(s) as rewriteable per se when performing a database only upgrade. During a database only upgrade the configure.php files are not modified. In fact only the store's configure.php file needs to be accessible for reading which means that the admin directory can already be renamed to your secret admin directory name and not reverted to admin. Just a little helpful hint for the live upgrade. Not to mention when the admin directory is renamed to admin, a message appears on the store side indicating that if the store is not still in maintenance mode. The message does not appear on the store side as long as the admin directory is not named admin....

[buildingblocks]

To ensure that it was all "one line" I would typically also go to the end of any row, remove the extra space(s) and then reinsert at least one space allowing the "text editor" to do with it what it may. Generally speaking I would say that it is an "ok" way to add it/modify it... Again the intent here would be to replace the existing query string with the new one as there is/was a problem with the old one.

Okay, wasn't sure from the previous description if each upgrade was painstakingly performed one at a time or all at once... There may also be value to the messages that were indicated, unless they all said something like skipped because already present or nothing to do because doesn't exist or some "informative" result like that.

As for the earlier description of doing the upgrade. There is no need to place the configure.php file(s) as rewriteable per se when performing a database only upgrade. During a database only upgrade the configure.php files are not modified. In fact only the store's configure.php file needs to be accessible for reading which means that the admin directory can already be renamed to your secret admin directory name and not reverted to admin. Just a little helpful hint for the live upgrade. Not to mention when the admin directory is renamed to admin, a message appears on the store side indicating that if the store is not still in maintenance mode. The message does not appear on the store side as long as the admin directory is not named admin....

I know to watch for spaces when working in the files but it didn't occur to me when in phpmyadmin. Thanks for the tip.

I did forget I didn't need to rename admin and adjust permissions. I could have saved myself a few steps. It gets done out of habit.

Thanks very much for helping mc12345678 and lat9. It's really appreciated.

[mc12345678]

I did forget I didn't need to rename admin and adjust permissions. I could have saved myself a few steps. It gets done out of habit.

Same here, but all it takes is one time of going through those motions and then selecting one of the other install methods and if not taking ones time, the database is gone and has to be restored from the backup that was just made... Fortunately hasn't happened to me, but now that I know that the zc_install process of upgrading the database doesn't require the admin directory to be renamed, I don't want to become a statistic of how many have "inadvertently" deleted/corrupted their database. :)

[lat9]

I'm glad you got it sorted out, but I'm still trying to figure out how the extra line-breaks were inserted. I've got a client that's being updated from Zen Cart v1.3.5 and that query is a single, unbroken line.

[buildingblocks]

I'm glad you got it sorted out, but I'm still trying to figure out how the extra line-breaks were inserted. I've got a client that's being updated from Zen Cart v1.3.5 and that query is a single, unbroken line.

I wish I could answer that one for you but I didn't do the original upgrade so I really don't know.