SQl Patch process - need instructions

Printable View

25 Nov 2019, 05:20 PM
dallison

SQl Patch process - need instructions

Brand new install, v156, php 7.2, installed with zc_install, only plugin installed TY tracker,
I am trying to perform a SQL patch for the dropship and pdf PO l\plugin but don't know how to use it under tools/install sql patches. I have copied and dropped the sql file provided into the window but get the following:

"WARNING: An Error occurred, please refresh the page and try again.If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields."

I'm sure this is because I don't understand what I am doing and have not entered something correctly. Can someone help?
25 Nov 2019, 08:35 PM
dallison

Re: SQl Patch process - need instructions

Quote:

Originally Posted by dallison

Brand new install, v156, php 7.2, installed with zc_install, only plugin installed TY tracker,
I am trying to perform a SQL patch for the dropship and pdf PO l\plugin but don't know how to use it under tools/install sql patches. I have copied and pasted the sql file contents provided into the window but get the following:

"WARNING: An Error occurred, please refresh the page and try again.If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields."

I'm sure this is because I don't understand what I am doing and have not entered something correctly. Can someone help?

Uploading the file results in the same error. The previous error I could find the forums included the deprecated TYPE command replaced with the ENGINE command to correct it but this has already been clompleted in this updated SQL patch so that is not the issue.
25 Nov 2019, 10:23 PM
dallison

Re: SQl Patch process - need instructions

Quote:

Originally Posted by dallison

Brand new install, v156, php 7.2, installed with zc_install, only plugin installed TY tracker,
I am trying to perform a SQL patch for the dropship and pdf PO l\plugin but don't know how to use it under tools/install sql patches. I have copied and dropped the sql file provided into the window but get the following:

"WARNING: An Error occurred, please refresh the page and try again.If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields."

I'm sure this is because I don't understand what I am doing and have not entered something correctly. Can someone help?

Here is the copy of the patch.

-- DROP TABLE IF EXISTS `subcontractors`;

CREATE TABLE `subcontractors` (
`subcontractors_id` int(10) unsigned NOT NULL auto_increment,
`short_name` varchar(20) NOT NULL default '',
`full_name` varchar(100) NOT NULL default '',
`street1` varchar(100) NOT NULL default '',
`city` varchar(255) NOT NULL default '',
`state` varchar(255) NOT NULL default '',
`zip` varchar(10) NOT NULL default '',
`email_address` varchar(100) NOT NULL default '',
`telephone` varchar(32) NOT NULL default '',
`contact_person` varchar(100) NOT NULL default '',
PRIMARY KEY (`subcontractors_id`)
) ENGINE=MyISAM COMMENT='subcontractors' AUTO_INCREMENT=0;

INSERT INTO subcontractors VALUES (0, 'ownstock', 'Own stock', 'Street','City','State','ZIP','[email protected]','telephone','contact name');

UPDATE subcontractors SET subcontractors_id=0 where short_name='ownstock';

ALTER TABLE `orders_products`
ADD `po_sent` char(1) NOT NULL default '0',
ADD `po_number` int(20),
ADD `po_sent_to_subcontractor` int(10),
ADD `po_date` DATE,
ADD `item_shipped` CHAR(1) NOT NULL default '0';

ALTER TABLE `products`
ADD `default_subcontractor` int(10) NOT NULL default '0';

SET @poid=9999;
SELECT (@poid:=configuration_group_id) as poid
FROM configuration_group
WHERE configuration_group_title= 'Purchase Orders';
DELETE FROM configuration WHERE configuration_group_id = @poid;
DELETE FROM configuration_group WHERE configuration_group_id = @poid;

INSERT INTO configuration_group VALUES ('', 'Purchase Orders', 'Purchase Order Settings', '1', '1');
UPDATE configuration_group SET sort_order = last_insert_id() WHERE configuration_group_id = last_insert_id();
SET @poid=9999;
SELECT (@poid:=configuration_group_id) as poid
FROM configuration_group
WHERE configuration_group_title= 'Purchase Orders';

DELETE FROM configuration WHERE configuration_key='PO_OWN_STOCK_EMAIL';
DELETE FROM configuration WHERE configuration_key='PO_NOTIFY';
DELETE FROM configuration WHERE configuration_key='PO_SUBJECT';

INSERT INTO configuration VALUES ('','PO - send packing lists', 'PO_SEND_PACKING_LISTS', '1', '0 - never, 1 - always, 2 - sometimes (default yes), 3 - sometimes (default no)', @poid, 101, now(), now(), NULL, NULL),
('','PO - notify customer', 'PO_NOTIFY', '1', '0 - no customer notification of PO updates, 1 - notify customer', @poid, 102, now(), now(), NULL, NULL),
('','PO - subject', 'PO_SUBJECT', '{contact_person}: New order (#{po_number}) for {full_name}', 'Subject of PO emails, {po_number} will be replaced with the actual number', @poid, 103, now(), now(), NULL, NULL),
('','PO - from email name', 'PO_FROM_EMAIL_NAME', 'PurchaseOrderManager', 'The FROM email NAME for sent Purchase Orders', @poid, 104, now(), now(), NULL, NULL),
('','PO - from email address', 'PO_FROM_EMAIL_ADDRESS', '[email protected]', 'The FROM email ADDRESS for sent Purchase Orders', @poid, 105, now(), now(), NULL, NULL),
('','PO - sent comments', 'PO_SENT_COMMENTS', 'Order Submitted to Shipping Department for Fulfillment', 'Comments added to the account when submitted to subcontractor', @poid, 106, now(), now(), NULL, NULL),
('','PO - full ship comments', 'PO_FULLSHIP_COMMENTS', 'Thanks for your order!', 'Comments added to the account when the order has shipped in full', @poid, 107, now(), now(), NULL, NULL),
('','PO - partial ship comments', 'PO_PARTIALSHIP_COMMENTS', 'Part of your order has shipped! The rest of your order will ship soon. You will be notified by email when your order is complete.', 'Comments added to the account when part of the order has shipped', @poid, 108, now(), now(), NULL, NULL),
('','PO - full ship packinglist', 'PO_FULLSHIP_PACKINGLIST', 'Thanks for your order!', 'Comments added to the packing list when the order has shipped in full', @poid, 109, now(), now(), NULL, NULL),
('','PO - partial ship packinglist', 'PO_PARTIALSHIP_PACKINGLIST', 'This is a partial shipment. The rest of your order has shipped or will ship separately.', 'Comments added to the packing list when part of the order has shipped', @poid, 110, now(), now(), NULL, NULL),
('','PO - packinglist filename', 'PO_PACKINGLIST_FILENAME', 'packinglist.pdf', 'packing list filename', @poid, 111, now(), now(), NULL, NULL),
('','PO - omit from unknown email 1', 'PO_UNKNOWN_OMIT1', '\nIf you would prefer to enter tracking information for this order\ndirectly, please visit:\n', 'Text to omit from emails sent for unknown customers 1 of 3', @poid, 112, now(), now(), NULL, NULL),
('','PO - omit from unknown email 2', 'PO_UNKNOWN_OMIT2', '{delivery_name}\n', 'Text to omit from emails sent for unknown customers 2 of 3', @poid, 113, now(), now(), NULL, NULL),
('','PO - omit from unknown email 3', 'PO_UNKNOWN_OMIT3', '', 'Text to omit from emails sent for unknown customers 3 of 3', @poid, 114, now(), now(), NULL, NULL),
('','PO - change shipping from', 'PO_CHANGE_SHIPPING_FROM', '', 'Change this shipping option to something else on POs and Packing Lists', @poid, 115, now(), now(), NULL, NULL),
('','PO - change shipping to', 'PO_CHANGE_SHIPPING_TO', 'Cheapest', 'Value to change shipping option to on POs and Packing Lists', @poid, 116, now(), now(), NULL, NULL);

INSERT INTO admin_pages (page_key,language_key,main_page,page_params,menu_key,display_on_menu,sort_order )
VALUES
('dropshipsendpos','BOX_CUSTOMERS_SEND_POS','FILENAME_SEND_POS',CONCAT('gID=',@c gi), 'customers', 'Y', @cgi),
('dropshipsendposnc','BOX_CUSTOMERS_SEND_POS_NC','FILENAME_SEND_POS_NC',CONCAT(' gID=',@cgi), 'customers', 'Y', @cgi),
('dropshipconftrack','BOX_CUSTOMERS_CONFIRM_TRACKING','FILENAME_CONFIRM_TRACKING ',CONCAT('gID=',@cgi), 'customers', 'Y', @cgi),
('dropshipeditsubs','BOX_TOOLS_EDIT_SUBCONTRACTORS','FILENAME_SUBCONTRACTORS',CO NCAT('gID=',@cgi), 'tools', 'Y', @cgi),
('dropshipsetsubs','BOX_TOOLS_SET_SUBCONTRACTORS','FILENAME_SET_SUBCONTRACTORS', CONCAT('gID=',@cgi), 'tools', 'Y', @cgi),
('configPurchaseOrders','BOX_CONFIGURATION_PURCHASEORDERS','FILENAME_CONFIGURATI ON',CONCAT('gID=',@poid), 'configuration', 'Y', @poid);
26 Nov 2019, 01:07 AM
mc12345678

Re: SQl Patch process - need instructions

When that message is displayed, the following FAQ can be used for initial guidance: https://www.zen-cart.com/content.php...-and-try-again
26 Nov 2019, 02:30 AM
dallison

Re: SQl Patch process - need instructions

Quote:

Originally Posted by mc12345678

When that message is displayed, the following FAQ can be used for initial guidance: https://www.zen-cart.com/content.php...-and-try-again

Thanks here are the resulst from the Log but it don't know if it helps.
tice 2019-11-24 21:58:03 67.21.32.110 1 dewey sqlpatch.php action=execute&debug=OFF 1 Admin SQL Patch tool executed a query. stdClass Object
(
[query_string] => -- DROP TABLE IF EXISTS `subcontractors`;

[debug] =>
[debug2] =>
[debug3] =)
Does this give any clues? Others must have experienced the same issue with a new install of this plugin.
26 Nov 2019, 02:45 AM
dallison

Re: SQl Patch process - need instructions

Quote:

Originally Posted by mc12345678

When that message is displayed, the following FAQ can be used for initial guidance: https://www.zen-cart.com/content.php...-and-try-again

Thanks here are the resulst from the Log but it don't know if it helps.
tice 2019-11-24 21:58:03 67.21.32.110 1 dewey sqlpatch.php action=execute&debug=OFF 1 Admin SQL Patch tool executed a query. stdClass Object
(
[query_string] => -- DROP TABLE IF EXISTS `subcontractors`;

[debug] =>
[debug2] =>
[debug3] =)
Does this give any clues? Others must have experienced the same issue with a new install of this plugin.
26 Nov 2019, 02:54 AM
balihr

Re: SQl Patch process - need instructions

You have quite a few errors in the SQL command. Here's a working version:

Code:

-- DROP TABLE IF EXISTS `subcontractors`; CREATE TABLE `subcontractors` ( `subcontractors_id` int(10) unsigned NOT NULL auto_increment, `short_name` varchar(20) NOT NULL default '', `full_name` varchar(100) NOT NULL default '', `street1` varchar(100) NOT NULL default '', `city` varchar(255) NOT NULL default '', `state` varchar(255) NOT NULL default '', `zip` varchar(10) NOT NULL default '', `email_address` varchar(100) NOT NULL default '', `telephone` varchar(32) NOT NULL default '', `contact_person` varchar(100) NOT NULL default '', PRIMARY KEY (`subcontractors_id`) ) ENGINE=MyISAM COMMENT='subcontractors' AUTO_INCREMENT=0; INSERT INTO subcontractors VALUES (0, 'ownstock', 'Own stock', 'Street','City','State','ZIP','[email protected]','telephone','contact name'); ALTER TABLE `orders_products` ADD `po_sent` char(1) NOT NULL default '0', ADD `po_number` int(20), ADD `po_sent_to_subcontractor` int(10), ADD `po_date` DATE, ADD `item_shipped` CHAR(1) NOT NULL default '0'; ALTER TABLE `products` ADD `default_subcontractor` int(10) NOT NULL default '0'; SET @poid=9999; SELECT (@poid:=configuration_group_id) as poid FROM configuration_group WHERE configuration_group_title= 'Purchase Orders'; DELETE FROM configuration WHERE configuration_group_id = @poid; DELETE FROM configuration_group WHERE configuration_group_id = @poid; INSERT INTO configuration_group VALUES ('', 'Purchase Orders', 'Purchase Order Settings', '1', '1'); UPDATE configuration_group SET sort_order = last_insert_id() WHERE configuration_group_id = last_insert_id(); SET @poid=9999; SELECT (@poid:=configuration_group_id) as poid FROM configuration_group WHERE configuration_group_title= 'Purchase Orders'; DELETE FROM configuration WHERE configuration_key='PO_OWN_STOCK_EMAIL'; DELETE FROM configuration WHERE configuration_key='PO_NOTIFY'; DELETE FROM configuration WHERE configuration_key='PO_SUBJECT'; INSERT INTO configuration (configuration_id, configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('','PO - send packing lists', 'PO_SEND_PACKING_LISTS', '1', '0 - never, 1 - always, 2 - sometimes (default yes), 3 - sometimes (default no)', @poid, 101, now(), now(), NULL, NULL), ('','PO - notify customer', 'PO_NOTIFY', '1', '0 - no customer notification of PO updates, 1 - notify customer', @poid, 102, now(), now(), NULL, NULL), ('','PO - subject', 'PO_SUBJECT', '{contact_person}: New order (#{po_number}) for {full_name}', 'Subject of PO emails, {po_number} will be replaced with the actual number', @poid, 103, now(), now(), NULL, NULL), ('','PO - from email name', 'PO_FROM_EMAIL_NAME', 'PurchaseOrderManager', 'The FROM email NAME for sent Purchase Orders', @poid, 104, now(), now(), NULL, NULL), ('','PO - from email address', 'PO_FROM_EMAIL_ADDRESS', '[email protected]', 'The FROM email ADDRESS for sent Purchase Orders', @poid, 105, now(), now(), NULL, NULL), ('','PO - sent comments', 'PO_SENT_COMMENTS', 'Order Submitted to Shipping Department for Fulfillment', 'Comments added to the account when submitted to subcontractor', @poid, 106, now(), now(), NULL, NULL), ('','PO - full ship comments', 'PO_FULLSHIP_COMMENTS', 'Thanks for your order!', 'Comments added to the account when the order has shipped in full', @poid, 107, now(), now(), NULL, NULL), ('','PO - partial ship comments', 'PO_PARTIALSHIP_COMMENTS', 'Part of your order has shipped! The rest of your order will ship soon. You will be notified by email when your order is complete.', 'Comments added to the account when part of the order has shipped', @poid, 108, now(), now(), NULL, NULL), ('','PO - full ship packinglist', 'PO_FULLSHIP_PACKINGLIST', 'Thanks for your order!', 'Comments added to the packing list when the order has shipped in full', @poid, 109, now(), now(), NULL, NULL), ('','PO - partial ship packinglist', 'PO_PARTIALSHIP_PACKINGLIST', 'This is a partial shipment. The rest of your order has shipped or will ship separately.', 'Comments added to the packing list when part of the order has shipped', @poid, 110, now(), now(), NULL, NULL), ('','PO - packinglist filename', 'PO_PACKINGLIST_FILENAME', 'packinglist.pdf', 'packing list filename', @poid, 111, now(), now(), NULL, NULL), ('','PO - omit from unknown email 1', 'PO_UNKNOWN_OMIT1', '\nIf you would prefer to enter tracking information for this order\ndirectly, please visit:\n', 'Text to omit from emails sent for unknown customers 1 of 3', @poid, 112, now(), now(), NULL, NULL), ('','PO - omit from unknown email 2', 'PO_UNKNOWN_OMIT2', '{delivery_name}\n', 'Text to omit from emails sent for unknown customers 2 of 3', @poid, 113, now(), now(), NULL, NULL), ('','PO - omit from unknown email 3', 'PO_UNKNOWN_OMIT3', '', 'Text to omit from emails sent for unknown customers 3 of 3', @poid, 114, now(), now(), NULL, NULL), ('','PO - change shipping from', 'PO_CHANGE_SHIPPING_FROM', '', 'Change this shipping option to something else on POs and Packing Lists', @poid, 115, now(), now(), NULL, NULL), ('','PO - change shipping to', 'PO_CHANGE_SHIPPING_TO', 'Cheapest', 'Value to change shipping option to on POs and Packing Lists', @poid, 116, now(), now(), NULL, NULL); INSERT INTO admin_pages (page_key,language_key,main_page,page_params,menu_key,display_on_menu,sort_order ) VALUES ('dropshipsendpos','BOX_CUSTOMERS_SEND_POS','FILENAME_SEND_POS',CONCAT('gID=',@cgi), 'customers', 'Y', @cgi), ('dropshipsendposnc','BOX_CUSTOMERS_SEND_POS_NC','FILENAME_SEND_POS_NC',CONCAT('gID=',@cgi), 'customers', 'Y', @cgi), ('dropshipconftrack','BOX_CUSTOMERS_CONFIRM_TRACKING','FILENAME_CONFIRM_TRACKING',CONCAT('gID=',@cgi), 'customers', 'Y', @cgi), ('dropshipeditsubs','BOX_TOOLS_EDIT_SUBCONTRACTORS','FILENAME_SUBCONTRACTORS',CONCAT('gID=',@cgi), 'tools', 'Y', @cgi), ('dropshipsetsubs','BOX_TOOLS_SET_SUBCONTRACTORS','FILENAME_SET_SUBCONTRACTORS',CONCAT('gID=',@cgi), 'tools', 'Y', @cgi), ('configPurchaseOrders','BOX_CONFIGURATION_PURCHASEORDERS','FILENAME_CONFIGURATION',CONCAT('gID=',@poid), 'configuration', 'Y', @poid);

Please note this WILL fail if you ran the SQL before and fields got added to orders_products and products tables. You can run the following query first to make sure it goes through:

Code:

ALTER TABLE orders_products DROP `po_sent`, DROP `po_number`, DROP `po_sent_to_subcontractor`, DROP `po_date`, DROP `item_shipped`; ALTER TABLE products DROP `default_subcontractor`;
29 Nov 2019, 02:36 PM
dallison

Re: SQl Patch process - need instructions

I ran the alter table code and then installed the corrected file you provided. Thank you very much. The following messages appeared at the top of the page after updating with the patch.
-17 statements processed
-cannot create table subcontractors already exists (shown 2 times)
-Note 1 statement ignored see upgrade_exceptions table for additional details

I don't know where to find the table can you point me in the right direction? Also at the end of one of the error callouts it lists PHP:171 . Is that a clue to help me find the error in the code? What does 171 mean? I am very new to this so any help is greatly appreciated. thanks again
29 Nov 2019, 02:53 PM
dallison

Re: SQl Patch process - need instructions

I am very sorry for not being clear on my last comments. I don't know where to find the "updrade exceptions" table referenced after the patch was installed.
Also the error I was referring to was from the mydebug-adm file I found under logs for the prior attempt of installing the patch with the errors you corrected. The error read trigger error then gave the location which was in the query factory file then listed , PHP:171. Is the 171 a location of something else?
thanks again
29 Nov 2019, 10:28 PM
balihr

Re: SQl Patch process - need instructions

I'm guessing the page didn't go blank.

- 17 statements processed means the NEW stuff was added to the database.
- cannot create table subcontractors already exists (shown 2 times) - simply ignore it. You've already created those tables so you can't create it again. If this were a fresh install, it would go through just fine.
- upgrade_exceptions table - you can use phpMyAdmin to look at your database. You'll find the table there. I believe you can ignore that as well.

- mydebug-adm file - was it created before or after you ran the new (fixed) sql query? If it's an old one, it's perfectly normal it's there. If it's a new one, please post the contents of the file here (hide your account-related data, of course). I'm not getting ANY mydebug logs when running that query.

Also, probably best - simply test the plugin and see if it works. If so, great!