Upgrade 4.11.3 - 4.11.4 Fails

Getting following error any ideas how to resolve this ?

Upgrade process of your store has failed

Please restore the pre-upgrade backup manually or via a link sent to your email address, then contact our technical support.


Tech details: Couldn't complete migration 20200304083836_core_change_processed_status.php. Reason: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND lang_code = 'en'' at line 1.

Completed migrations: 5 out of 16.

aaa.jpg

adapter->getOptions();
        $pr = $options['prefix'];
        $status_id = $this->fetchRow("SELECT status_id FROM {$pr}statuses WHERE status = 'P' AND type = 'O' AND is_default = 'Y'");
        $this->query("UPDATE {$pr}status_descriptions SET description = 'Paid', email_subj = 'has been paid', email_header = 'Your order has been paid successfully.' WHERE status_id = {$status_id['status_id']} AND lang_code = 'en'");
    }

    public function down()
    {
        $options = $this->adapter->getOptions();
        $pr = $options['prefix'];
        $status_id = $this->fetchRow("SELECT status_id FROM {$pr}statuses WHERE status = 'P' AND type = 'O' AND is_default = 'Y'");
        $this->query("UPDATE {$pr}status_descriptions SET description = 'Processed', email_subj = 'has been processed', email_header = 'Your order has been processed successfully.' WHERE status_id = {$status_id['status_id']} AND lang_code = 'en'");
    }
}

20200304083836_core_change_processed_status.php

I guess theres too many order statuses here

statuses.jpg

2020-12-02 13:14:35:  == 20200304083836 CoreChangeProcessedStatus: migrating
2020-12-02 13:14:35: Tygh\Exceptions\DatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND lang_code = 'en'' at line 1 in /home/xxxx/public_html/dev/app/Tygh/UpgradeCenter/Phinx/MysqliAdapter.php:166
Stack trace:
#0 /home/xxxx/public_html/dev/app/Tygh/UpgradeCenter/Phinx/MysqliAdapter.php(108): Tygh\UpgradeCenter\Phinx\MysqliAdapter->onQueryError('UPDATE cscart_s...')
#1 /home/xxxx/public_html/dev/app/lib/vendor/robmorgan/phinx/src/Phinx/Migration/AbstractMigration.php(169): Tygh\UpgradeCenter\Phinx\MysqliAdapter->query('UPDATE cscart_s...')
#2 /home/xxxx/public_html/dev/var/upgrade/packages/core/content/migrations/20200304083836_core_change_processed_status.php(12): Phinx\Migration\AbstractMigration->query('UPDATE cscart_s...')
#3 /home/xxxx/public_html/dev/app/lib/vendor/robmorgan/phinx/src/Phinx/Migration/Manager/Environment.php(116): CoreChangeProcessedStatus->up()
#4 /home/xxxx/public_html/dev/app/lib/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(210): Phinx\Migration\Manager\Environment->executeMigration(Object(CoreChangeProcessedStatus), 'up')
#5 /home/xxxx/public_html/dev/app/lib/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(186): Phinx\Migration\Manager->executeMigration('development', Object(CoreChangeProcessedStatus), 'up')
#6 /home/xxxx/public_html/dev/app/lib/vendor/robmorgan/phinx/src/Phinx/Console/Command/Migrate.php(100): Phinx\Migration\Manager->migrate('development', 20200429132857)
#7 /home/xxxx/public_html/dev/app/lib/vendor/symfony/console/Symfony/Component/Console/Command/Command.php(257): Phinx\Console\Command\Migrate->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Tygh\UpgradeCenter\Migrations\Output))
#8 /home/xxxx/public_html/dev/app/lib/vendor/symfony/console/Symfony/Component/Console/Application.php(874): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Tygh\UpgradeCenter\Migrations\Output))
#9 /home/xxxx/public_html/dev/app/lib/vendor/symfony/console/Symfony/Component/Console/Application.php(195): Symfony\Component\Console\Application->doRunCommand(Object(Phinx\Console\Command\Migrate), Object(Symfony\Component\Console\Input\ArgvInput), Object(Tygh\UpgradeCenter\Migrations\Output))
#10 /home/xxxx/public_html/dev/app/lib/vendor/robmorgan/phinx/src/Phinx/Console/PhinxApplication.php(80): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Tygh\UpgradeCenter\Migrations\Output))
#11 /home/xxxx/public_html/dev/app/lib/vendor/symfony/console/Symfony/Component/Console/Application.php(126): Phinx\Console\PhinxApplication->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Tygh\UpgradeCenter\Migrations\Output))
#12 /home/xxxx/public_html/dev/app/Tygh/UpgradeCenter/Migrations/Migration.php(103): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Tygh\UpgradeCenter\Migrations\Output))
#13 /home/xxxx/public_html/dev/app/Tygh/UpgradeCenter/App.php(733): Tygh\UpgradeCenter\Migrations\Migration->__call('migrate', Array)
#14 /home/xxxx/public_html/dev/app/Tygh/UpgradeCenter/App.php(463): Tygh\UpgradeCenter\App->installUpgradePackage('core', Array)
#15 /home/xxxx/public_html/dev/app/controllers/backend/upgrade_center.php(133): Tygh\UpgradeCenter\App->install('core', Array)
#16 /home/xxxx/public_html/dev/app/functions/fn.control.php(679): include('/home/tba/domai...')
#17 /home/xxxx/public_html/dev/app/functions/fn.control.php(430): fn_run_controller('/home/tba/domai...', 'upgrade_center', 'install', '', '')
#18 /home/xxxx/public_html/dev/admin.php(27): fn_dispatch()
#19 {main}
2020-12-02 13:14:35: Failed to execute migrations

OK fixed this myself.

So as said here

    public function down()
    {
        $options = $this->adapter->getOptions();
        $pr = $options['prefix'];
        $status_id = $this->fetchRow("SELECT status_id FROM {$pr}statuses WHERE status = 'P' AND type = 'O' AND is_default = 'Y'");
        $this->query("UPDATE {$pr}status_descriptions SET description = 'Processed', email_subj = 'has been processed', email_header = 'Your order has been processed successfully.' WHERE status_id = {$status_id['status_id']} AND lang_code = 'en'");
    }

in db

cscart_status_descriptions;

mine was different named in

INSERT INTO cscart_status_descriptions (`status_id`, `description`, `email_subj`, `email_header`, `lang_code`) VALUES ('1', 'BLA BLA', 'BLA BLA BLA', 'BLA', 'en');

also in

cscart_statuses

was set as statuses not default meaning N changed to Y

INSERT INTO cscart_statuses (`status_id`, `status`, `type`, `is_default`, `position`) VALUES ('1', 'P', 'O', 'Y', '0');

basically script was looking for 100% match not modified statuses and descriptions. Why the heck would it look for perfect match if those fields in old stores were editable and easy changeable..

To answer your question, because there were always a certain set of status codes that were constant (unchangeable) in cs-cart and hence those were used. It would appear from your post in #2 above that $status_id is being returned as an empty array because the search is failing. It is then referenced for $status_id[['status_id'] when 'status_id' index is undefined. There is probably an associated NOTICE in your php error_log related to it if you have the NOTICE messages enabled.

Note also that your VALUES should not quote integer or decimal values. They are NOT strings. And if the DB is run in strict mode, that query will fail.

I have the same problem ! Any idea how to fix it?

I too have this issue, and to my knowledge and notes, the only thing I have changed in any of my order statuses is the Email subject: and of course the Email header:...Name:, Status: and other info has been left stock.

Would changing the Email subject: and Email header: fields cause this upgrade error, despite not having caused this error in many previous upgrades?

You can run the sql attached taken from the CSC demo to fix the inconsistencies in the cscart_statuses and cscart_status_descriptions tables. Of course this will erase anything you may have added/modified previously. Then run the upgrade again and it should be fine.

backup_4.12.2_24Mar2021_123448.sql

I too have this issue, and to my knowledge and notes, the only thing I have changed in any of my order statuses is the Email subject: and of course the Email header:...Name:, Status: and other info has been left stock.

Would changing the Email subject: and Email header: fields cause this upgrade error, despite not having caused this error in many previous upgrades?

Yes this is what causes issue. It is long ago you cannot modify these texts, but if you are upgrading from very early version you might have changed text as it was possible to do then..

Thanks for the comments.

So if I understand correctly then, I (and countless others?) have two options...a ) run Tool's restore and lose everything I've ever customized (WTH?!), or b ) not be able to upgrade past 4.11.3.

I must be missing something.

Thanks for the comments.

So if I understand correctly then, I (and countless others?) have two options...a ) run Tool's restore and lose everything I've ever customized (WTH?!), or b ) not be able to upgrade past 4.11.3.

I must be missing something.

Not sure what exactly you are loosing ? :)

You got two options

Either you do all change manually via phpmyadmin or you run tools sql file. Have you at least looked at it?

Personally I did all manually, by editing each field that did not match original. I did this because I have few additional statuses (do not wanted to loose those) that generates invoice number and credit memo.

Upgrade script is checking if status names and their description match these below, if not it stops..

INSERT INTO cscart_status_descriptions (`status_id`, `description`, `email_subj`, `email_header`, `lang_code`) VALUES ('2', 'Complete', 'has been completed', 'Your order has been completed. Thank you for choosing us.', 'en');
INSERT INTO cscart_status_descriptions (`status_id`, `description`, `email_subj`, `email_header`, `lang_code`) VALUES ('1', 'Paid', 'has been paid', 'Your order has been paid successfully.', 'en');
INSERT INTO cscart_status_descriptions (`status_id`, `description`, `email_subj`, `email_header`, `lang_code`) VALUES ('3', 'Open', 'has been placed successfully.', 'Thank you for the order. You are welcome to come back!', 'en');
INSERT INTO cscart_status_descriptions (`status_id`, `description`, `email_subj`, `email_header`, `lang_code`) VALUES ('4', 'Failed', 'failed', 'Your order has been unsuccessful. Please contact shop administration.', 'en');
INSERT INTO cscart_status_descriptions (`status_id`, `description`, `email_subj`, `email_header`, `lang_code`) VALUES ('5', 'Declined', 'has been declined', 'Your order has been declined. Please contact shop administration.', 'en');
INSERT INTO cscart_status_descriptions (`status_id`, `description`, `email_subj`, `email_header`, `lang_code`) VALUES ('6', 'Backordered', 'has been backordered', 'Your order has been backordered.', 'en');
INSERT INTO cscart_status_descriptions (`status_id`, `description`, `email_subj`, `email_header`, `lang_code`) VALUES ('7', 'Canceled', 'has been canceled', 'Your order has been canceled. Please contact shop administration.', 'en');
INSERT INTO cscart_status_descriptions (`status_id`, `description`, `email_subj`, `email_header`, `lang_code`) VALUES ('8', 'Picked up', '', '', 'en');
INSERT INTO cscart_status_descriptions (`status_id`, `description`, `email_subj`, `email_header`, `lang_code`) VALUES ('9', 'Packed', '', '', 'en');
INSERT INTO cscart_status_descriptions (`status_id`, `description`, `email_subj`, `email_header`, `lang_code`) VALUES ('10', 'Shipped', '', '', 'en');

Additionally all above statuses need to be set as default Y in cscart_statuses

Thank you for the further explanations everyone.

So to clarify, if I change the email_subj and email_header back to original (manually or with script), then upgrade to 4.11.4, I will then be able to edit my email_subj and email_header customizations again AND future upgrades will be successful without failing? OR will I always need to edit the email_subj and email_header back to original before each desired upgrade thereafter?

Email headers are handled in email templates in latest cart versions…

Ah, understood, wasn't aware those customizations would be located elsewhere in the backend...would be very strange for CSCart to take that ability away.

So, in later versions, there are different email templates available for each status?

https://demo.cs-cart.com/admin.php?dispatch=notification_settings.manage&receiver_type=C

Yes, was looking at demo a little last night. Didn't catch some other changes in changelog either, so will need to wait a bit for more time to upgrade/test/customize.

Thanks again all, at least I know more of what to expect from 4.11.4-x.x.x.

Hello all, revisiting this now that I had time to look into this, in case anyone is still interested.

Because I was still confused with the instructions in this thread (which I'm sure are correct and appreciated), I asked CS's Helpdesk (thank you!) for an explanation. Our installation is still mostly stock and I couldn't imagine us needing to do the various tasks detailed in this thread to get the upgrade to go. So, the following...

- apparently for us, in table cscart_statuses, there was an existing row with status "P", type "O", and is_default "N". The default being "N" instead of "Y" is what Helpdesk said could possibly be causing our error and failure to upgrade past 4.11.3.

- I went into the database and edited that row for "Y". No other changes were made to any other entry or in any other table.

- I made my numerous backups, and was able to upgrade to 4.11.4 without error. (I ran SP1, SP2, and SP3 upgrades immediately after as well, before doing any testing, and no errors when upgrading to those either.)

- I did need to go back to the stock 'Open' status in Admin and customize our title, email heading, and html as we had before, since that was lost in the upgrade(s) (which could happen as mentioned earlier in this thread). However, no other statuses in our installation have changed in any way that I'm aware of.

- I am now running 4.11.4.SP3, and all appears to be working correctly as before. I will run this for awhile before going further with any other upgrades.

So, again, not sure if all of the prior instructions in this thread would have done the same as what I did, but simply editing that one row for us allowed the upgrade(s) to progress.

Sorry for the lecture, but perhaps this will help those who are as inept as me. Thanks again to all.