1.5.6 ezpages split table structure

[mc12345678]

This looks similar to the issue I'm having, 1.5.6 upgrade said it was successful but it didn't add the tables or data. Still don't know how to fix this.

There's the just make it work fix and there's the how to prevent having to make it work. Of the two or three people that have reported this issue, there is information (in part see the posting tips) that if provided would help to not require any repair. To my knowledge those that typically help with these matters haven't experienced the issue so are unable to fix a problem that can't be recreated... the more information that can be provided about the background and current system, the easier it should be to find a solution that prevents this issue occuring.

[JNR345]

Thank you, mc12345678. I really do appreciate your help. But I do think it will be much more common in the coming days as people get back from the holidays. I have had the same issue on three different web hosts, two which are popular: Just Host and Network Solutions. They are all up to date with PHP versions and MYSQL. I tried other php versions as well that made no difference (5.6, 7.0, 7.1). I don't think it matters which web host you use. Any host will probably have this issue as there was never any issue with previous versions of Zen Cart (upgrades always went smoothly). Something is wrong with the zc_install in 1.5.6 it seems (it doesn't even recognize that it's not adding the tables and data correctly). It congrats you on a successful installation. Whatever was changed in zc_install from 1.5.5f needs to be looked at. Of course I have no idea about much of this but seems that is the issue. Thanks again to you and DrByte for all the work you do.

[mc12345678]

Please be more specific with "latest" versions. For example, on one host, I expected to find the latest php; however, the highest they currently offer is 7.2 and 7.3 has been released.

There are potentially other factors involved that may play cause the issue, finding the common ground is what is needed to correct it. I know that I plan today to run through the zc_install upgrade on some historically kept databases based on the reports, but if I can't reproduce the issue with those databases then I know I can't help the core developers.

Personally, while I know I can correct the resulting problem, I have way more useful things to do with my time and think that others would benefit from it working like expected rather than having to spend their time doing who knows what to make it work.

[mc12345678]

So, here's a report that I have although I continue to seek solution to this and one other issue I found.

In the original update of a database that had been built using ZC 1.5.5b, but is now being reported as needing an upgrade to version 1.5.5 from 1.5.4 (hadn't upgraded the software necessarily to ZC 1.5.5f), so when I ran zc_install on this restored database I was advised that needed to first upgrade to ZC 1.5.5 from 1.5.4 before upgrading to ZC 1.5.6.

The next thing seen in that update was that a table which had a field of type date and a default of NULL. When attempting to do the upgrade there was a message in the install log that one of the records had an incorrect date value (plugin related) with the value being '0000-00-00 00:00:00', When I changed the saved data to '0001-01-01 00:00:00' and again ran zc_install, then I was presented with a new/different problem.

The newer problem was that the install log identified:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]MySQL error 1146 encountered during zc_install:
Table 'DB_DATABASE.languages' doesn't exist
LEFT JOIN languages l ON 1;[/FONT]

note that DB_DATABASE was used above in place of the actual database name. The issue is that the dataset does use a DB_PREFIX which wasn't appended to the zc_install line in processing the left join code. This is something related to the zc_install process and formation of the sql statement in this area. Looking into that now.

Yup, unfortunately there is an expectation that LEFT JOIN is a function that exists and handles the condition; however, the parserLeftJoin function does not exist and therefore the prefix for the database does not get added when calling this line of the database upgrade. What this means is that right now for a site that has a DB_PREFIX and ezpages that they will be lost in the upgrade.

So, I've prepped a few modifications for incorporation to address the easier things in the zc_install process. Still need to come up with a convenient way to address possibilities where a date or datetime field perhaps are set to allow being null but then have an entry of '0000-00-00' instead of null or the ZC '0001-01-01'. DrByte has gone ahead and added at least one field that was found to be consistently an issue, I'm trying to figure out how/why the field I came across is logged that way so that can prevent any future attempts, but another thing that may help in this matter is the zc_install process reporting one or more of the issues encountered or an improved method to not initiate an action based on some previous response... really this database issue that is in a way unrelated with the actual operations is a kicker...

Anyways, my github submission for part of the issues identified/seen: https://github.com/zencart/zencart/pull/1981

The files or edits to them are: https://github.com/zencart/zencart/pull/1981/files

This is still under review by the admin's. I believe I resolved the issue of the ezpage data not being copied at least for databases that use a DB_PREFIX.
Also, there may be more data logged as a result of correcting a filename reference in a couple of spots.

Really sorry for this rambling, it's been broken up over several hours and I want to move on to something else.

[welshop.com]

nice work mc12345678

[mc12345678]

For the time being, recommend still looking through the install logs after installing but before accessing admin to see if there are any issues reported about '0000-00-00'.

But, for those that have reported issues related to the install and then applied the above patch, it would be nice to know the success/failure of it and differences in reported information from previous. (trying to get to the root of the issue(s) so that can advise best, I certainly don't look forwards to the support inquiries caused by the date/datetime issue if it can't be corrected more easily in the code/sql.)

[welshop.com]

Details of server config of my 1.5.6 test site where I had the EzPage table issue are:
PHP Version 5.6.39
Database Engine: MySQL 10.1.37-MariaDB
Original zenCart version: v1.5.1

ps: I also had the '0000-00-00' issue with the configuration table but I just manually amended those fields prior to re-running the DB install upgrade and that resolved the issue

Hope this helps

[mc12345678]

Details of server config of my 1.5.6 test site where I had the EzPage table issue are:
PHP Version 5.6.39
Database Engine: MySQL 10.1.37-MariaDB
Original zenCart version: v1.5.1

ps: I also had the '0000-00-00' issue with the configuration table but I just manually amended those fields prior to re-running the DB install upgrade and that resolved the issue

Hope this helps

Thank you for that added information. I haven't inspected the code for this, but it does seem like the zc_install may be using some sort of strict database techniques. I don't think that I have anything activated on my setup to be so picky about some other field within a table where the field touched has nothing to do with the one that causes the error. Anyways, the '0000-00-00' I experienced was related to a plugin that modified the orders table. This is why some "detection" process or other interference check would be nice. Haven't had the conversation yet, but wondering if it should be a more php based solution or one that has involved sql.

Basically the database needs to be tidied up more up front to ensure that the existing data remains intact and the update performed... sure, I think there are a few limitations on what to expect as a solution, but...

Hmm... wondering how hard it would be for the upgrade test to be run again at "completion" to see if there is a report of needing to still upgrade? Again though that doesn't help with the entirety of the potential ezpages issue.

Backup, backup, backup!!!

[welshop.com]

I will do a test on your patches tomorrow and let you know the results.

[DrByte]

it does seem like the zc_install may be using some sort of strict database techniques. I don't think that I have anything activated on my setup to be so picky about some other field within a table where the field touched has nothing to do with the one that causes the error.

No, that's all from MySQL's own strict-mode.
Specifically, if you're trying to ALTER a table's schema, and ANY records already in the table violate any of the MySQL strict rules in effect according to its current configuration, it will throw an error and fail to do the ALTER even if the ALTER command has nothing to do with the "invalid" data.
In this case ZC just wanted to add another new (non-date-related) field to the db schema, but it failed because existing records had invalid dates, it rejected the whole statement. (This is in part because adding a new field requires editing every record already in the db, to add the new field into each record.)

That's the technical "why" behind what's happening.

ZC has done lots of date-related updates in prior upgrades including v151 v153 v155. This val_function addition was pulled down to v156 from v2 and we missed backporting the db date-fix for the same table.


Ironically this has nothing to do with the ezpages changes that started this thread.

wondering how hard it would be for the upgrade test to be run again at "completion" to see if there is a report of needing to still upgrade

It would actually be quite a pain. The "upgrade detection test" only looks for a couple clear data points. It can't fairly check for every individual change, since sometimes those "changes" are legitimately altered by the end-user post-install, and if the upgrade-detection were to test for those then it would always fail.

Remember, all these upgrades should be done first in a test copy of the site, where all "issues" are sorted out prior to actually upgrading the "live" site. Thus the actual upgrade applied to the live site won't have a need to re-run any of the steps since you first tested it against your own data offline.