PayPal IPN Checkout issues: IPN Admin page & PP requiring signup

[Veazer]

After upgrading a Zencart v1.2.4 site to v1.3.6, I noticed the following error when I got to Customers --> PayPal IPN:

1054 Unknown column 'p.zen_order_id' in 'on clause'
in:
[select count(*) as total from paypal as p left join orders as o on o.orders_id = p.zen_order_id]
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.

Also, attemps to access my 'ipn_main_handler.php' handler page via browser result in the error:

1054 Unknown column 'zen_order_id' in 'field list'
in:
[SELECT zen_order_id, paypal_ipn_id, payment_status, txn_type, pending_reason FROM paypal WHERE txn_id = '' ORDER BY zen_order_id DESC ]

No other ill effects from the database upgrade have been noticed, if that's the cause.

Second issue: PayPal is requiring customers to create a PayPal account despite the fact that the setting in "PayPal Account Optional" in Profile --> Website Payment Preferences is set to TRUE. This was previously working but may have stopped working before the 1.3.6 upgrade, i'm not certain.

Any thoughts or advice?

[DrByte]

1. The zen_order_id field was added to the "paypal" database table in the upgrade from v1.2.1 to 1.2.2. Seems like you must have missed that step from an earlier upgrade.

2. There's not much Zen Cart can do about PayPal requiring a customer to have a PayPal account in order to complete a payment. However, you might note that a PayPal "business" account allows non-PayPal customers to pay you, but "premier" and "personal" accounts are less flexible. You might want to explore this matter with PayPal customer service.

3. Zen Cart v1.3.7 is out and has a huge number of PayPal improvements, including the addition of PayPal Express Checkout. You might want to explore these options as well.

[Veazer]

1. The zen_order_id field was added to the "paypal" database table in the upgrade from v1.2.1 to 1.2.2. Seems like you must have missed that step from an earlier upgrade.

2. There's not much Zen Cart can do about PayPal requiring a customer to have a PayPal account in order to complete a payment. However, you might note that a PayPal "business" account allows non-PayPal customers to pay you, but "premier" and "personal" accounts are less flexible. You might want to explore this matter with PayPal customer service.

3. Zen Cart v1.3.7 is out and has a huge number of PayPal improvements, including the addition of PayPal Express Checkout. You might want to explore these options as well.

Thanks for your help with this... After browsing the forums for a long time I've seen how much you've contributed to the ZC community. Thanks

1) this installation started at 1.2.4, i never used 1.2.1 or 1.2.2. Can I simply add these fields to the DB using phpMyAdmin?

2) I just thought maybe this was occuring because PP is not receiving something from ZC that it should be. I know it's an option for the Premier Accounts so it must be supported... By 'business' account do you mean the PayPal Pro account?

3) Ooops. I meant to say 1.3.7, typo. Repeatedly. It was never 1.3.6. I'd love to use PP Express CO but as I understand it only the Pro accounts support it.

[DrByte]

PayPal Express Checkout is supported by Business Accounts. (although some have experienced success with just Premier accounts too)
That does NOT have anything to do with "Pro".
PayPal offers 3 account types: personal, premier, business. The differences are documented on their website.

"Pro" is just the ability to accept credit cards directly on your site without the customer ever going to the PayPal site to make payment.

[DrByte]

1) this installation started at 1.2.4, i never used 1.2.1 or 1.2.2. Can I simply add these fields to the DB using phpMyAdmin?

a. I have no idea what *else* may be missing from your database
b. you can fix the "specific" problem with the column-not-found error by running this via phpMyAdmin:

ALTER TABLE paypal ADD COLUMN zen_order_id int(17) NOT NULL default '0' AFTER paypal_ipn_id;

I make no guarantees about anything else. I'm very surprised that you're missing that field, as it's been there from a long time back.

[DrByte]

Have you installed the latest patch update for PayPal and v1.3.7?
http://www.zen-cart.com/forum/showthread.php?t=56216

[Veazer]

PayPal Express Checkout is supported by Business Accounts. (although some have experienced success with just Premier accounts too)
That does NOT have anything to do with "Pro".
PayPal offers 3 account types: personal, premier, business. The differences are documented on their website.

"Pro" is just the ability to accept credit cards directly on your site without the customer ever going to the PayPal site to make payment.

Thanks for the clarification. Their website does not explain this sufficiently imho, users need to actually click signup to find a page detailing the differences. The best I could find before that was this comparison page that just shows differences between standard, pro, etc. but doesn't even mention personal, premier and business account. That said, this is indeed a business account.

I thought Paypal express checkout was the same as Pro (ie, accepting cards directly on your site) so that only compounded my confusion. Since installing PP EC I see the differences.

a. I have no idea what *else* may be missing from your database
b. you can fix the "specific" problem with the column-not-found error by running this via phpMyAdmin:

ALTER TABLE paypal ADD COLUMN zen_order_id int(17) NOT NULL default '0' AFTER paypal_ipn_id;

I make no guarantees about anything else. I'm very surprised that you're missing that field, as it's been there from a long time back.

I think I beginning to see the cause of the problem now, but I am unsure how to fix it. And I’m scared. I'll explain my [possibly botched] upgrade procedure, IIRC

I backed up the DB from the 1.2.4 store
I created a second DB on the server from this backup.
I created a fresh install of 1.3.7 and selected the new DB
During install, ZC recognized the old DB and upgraded the DB to 1.3.7

The problem is my old DB had 'zen_' prefixes for all fields and the new DB does not. I'm not sure how i managed that, i'll claim negligence on my part...

Therefore, it seems that ZC properly recognizes the fields in the new DB lack the 'zen_' prefix (all my customers, products and orders are present) except for the paypal module which is still looking for ‘zen_order_id’ when it should be looking for just ‘order_id’? I do indeed have ‘order_id’ on the ‘paypal’ table and the old DB has ‘zen_order_id’ on the ‘zen_paypal’ table. (Which seems a little strange, since none of the other fields on the ‘zen_paypal’ table have the ‘zen_’ prefix… Perhaps a naming scheme change since 1.2.4? )

Is there a way for me to make sure the PP modules are looking for the right table & fields since we can see they are looking for the old ones?

Have you installed the latest patch update for PayPal and v1.3.7?
http://www.zen-cart.com/forum/showthread.php?t=56216

Just did it after reading your reply. It fixed the ‘ipn_main_handler.php’ error but I still get the other error attempting to access the IPN page in admin.

Thanks again.

[Veazer]

...Which seems a little strange, since none of the other fields on the ‘zen_paypal’ table have the ‘zen_’ prefix…

Yay! This seems to be the problem. Somehow the 'order_id' field from the 'zen_paypal' table of the 1.2.4 got a prefix as well, rather than just all the tables. If I rename the 'order_id' field in the new db to 'zen_order_id' then everything seems to work fine.

I assume my fix is a hack, though, and the preferred method would be to leave the db as it was and change whatever is looking for 'zen_order_id', right?

[DrByte]

It's always best not to change any database structural configuration unless you know exactly what you're doing.

The correct structure of the "paypal" table is:

CREATE TABLE paypal (
  paypal_ipn_id int(11) unsigned NOT NULL auto_increment,
  zen_order_id int(11) unsigned NOT NULL default '0',
  txn_type varchar(40) NOT NULL default '',
  reason_code varchar(15) default NULL,
  payment_type varchar(40) NOT NULL default '',
  payment_status varchar(32) NOT NULL default '',
  pending_reason varchar(32) default NULL,
  invoice varchar(128) default NULL,
  mc_currency char(3) NOT NULL default '',
  first_name varchar(32) NOT NULL default '',
  last_name varchar(32) NOT NULL default '',
  payer_business_name varchar(128) default NULL,
  address_name varchar(64) default NULL,
  address_street varchar(254) default NULL,
  address_city varchar(120) default NULL,
  address_state varchar(120) default NULL,
  address_zip varchar(10) default NULL,
  address_country varchar(64) default NULL,
  address_status varchar(11) default NULL,
  payer_email varchar(128) NOT NULL default '',
  payer_id varchar(32) NOT NULL default '',
  payer_status varchar(10) NOT NULL default '',
  payment_date datetime NOT NULL default '0001-01-01 00:00:00',
  business varchar(128) NOT NULL default '',
  receiver_email varchar(128) NOT NULL default '',
  receiver_id varchar(32) NOT NULL default '',
  txn_id varchar(20) NOT NULL default '',
  parent_txn_id varchar(20) default NULL,
  num_cart_items tinyint(4) unsigned NOT NULL default '1',
  mc_gross decimal(7,2) NOT NULL default '0.00',
  mc_fee decimal(7,2) NOT NULL default '0.00',
  payment_gross decimal(7,2) default NULL,
  payment_fee decimal(7,2) default NULL,
  settle_amount decimal(7,2) default NULL,
  settle_currency char(3) default NULL,
  exchange_rate decimal(4,2) default NULL,
  notify_version decimal(2,1) NOT NULL default '0.0',
  verify_sign varchar(128) NOT NULL default '',
  last_modified datetime NOT NULL default '0001-01-01 00:00:00',
  date_added datetime NOT NULL default '0001-01-01 00:00:00',
  memo text,
  PRIMARY KEY (paypal_ipn_id,txn_id),
  KEY idx_zen_order_id_zen (zen_order_id)
) TYPE=MyISAM;

[Veazer]

It's always best not to change any database structural configuration unless you know exactly what you're doing.

The correct structure of the "paypal" table is:

CREATE TABLE paypal (
  paypal_ipn_id int(11) unsigned NOT NULL auto_increment,
  zen_order_id int(11) unsigned NOT NULL default '0',
  txn_type varchar(40) NOT NULL default '',
  reason_code varchar(15) default NULL,
  
...[excess deleted}

Ahh! Then that means that there might be a bug in zencart's removal of prefixes in the DB, correct? After my upgrade, my paypal table only had 'order_id'. It looks like it must have searched the full DB for instances of 'zen_' and removed them, including those that needed to be there. Should I report this to a developer or would that include you?

I really appreciate your help in solving this issue.