Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    136
    Plugin Contributions
    0

    Default My v1.2 database cannot be upgraded using ZC_install

    Since this seems to be it's own issue, I'm starting a concurrent thread with one I already have going regarding difficulty upgrading from 1.2 to 1.3.7.1

    I have what seems to be a perfectly functioning zencart v1.2. My database seems to be v1.1.4 patch1.

    I've created a new install of zencart 1.3.7.1 and have created a new database for it. My understanding is that after I've run the zencart installer, in order to upgrade my old database, I must drop the tables in the new database, import the old database then run ZC_install again in order to upgrade the database to the current schema. If that assumption is incorrect, maybe that helps to explaiin why I am having an impossible time getting it to work.

    So based on what I've just described, when I get to the point where I am to upgrade the database, as soon as I attmept to upgrade I get the following error...

    1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(paypal_ipn p,zen_paypal_ipn_orders_memo pm LEFT JOIN zen_paypal_ipn_orders po O' at line 1
    in:
    [INSERT INTO zen_paypal (paypal_ipn_id, txn_type, reason_code, payment_type, payment_status, pending_reason, invoice, mc_currency, first_name, last_name, payer_business_name, address_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_id, payer_status, payment_date, business, receiver_email, receiver_id, txn_id, num_cart_items, mc_gross, mc_fee, payment_gross, payment_fee, settle_amount, exchange_rate, notify_version, verify_sign, date_added, memo ) SELECT p.paypal_ipn_id, p.txn_type, p.reason_code, p.payment_type, p.payment_status, p.pending_reason, p.invoice, p.mc_currency, p.first_name, p.last_name, p.payer_business_name, p.address_name, p.address_street, p.address_city, p.address_state, p.address_zip, p.address_country, p.address_status, p.payer_email, p.payer_id, p.payer_status, p.payment_date, p.business, p.receiver_email, p.receiver_id, p.txn_id, po.num_cart_items, po.mc_gross, po.mc_fee, po.payment_gross, po.payment_fee, po.settle_amount, po.exchange_rate, p.notify_version, p.verify_sign, p.date_added, pm.memo FROM zen_(paypal_ipn p,zen_paypal_ipn_orders_memo pm LEFT JOIN zen_paypal_ipn_orders po ON p.paypal_ipn_id = po.paypal_ipn_id) WHERE p.paypal_ipn_id = pm.paypal_ipn_id;]

    So what's the deal? My database works fine under v1.2 of Zencart but then the upgrade process fails and I'm left scratching my head.

  2. #2
    Join Date
    Mar 2004
    Posts
    136
    Plugin Contributions
    0

    Default Re: Problem: mysql SQL Install file does not exist

    I dumped the database again and imported a backup from February 2007. I ran the installer again to upgrade the database and the installer sniffer predicted an upgrade from v 1.2.0 to 1.2.1

    I deselected all the other checkboxes except for the recommended upgrade and clicked "update database now". This window opened up...

    1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(paypal_ipn p,zen_paypal_ipn_orders_memo pm LEFT JOIN zen_paypal_ipn_orders po O' at line 1
    in:
    [INSERT INTO zen_paypal (paypal_ipn_id, txn_type, reason_code, payment_type, payment_status, pending_reason, invoice, mc_currency, first_name, last_name, payer_business_name, address_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_id, payer_status, payment_date, business, receiver_email, receiver_id, txn_id, num_cart_items, mc_gross, mc_fee, payment_gross, payment_fee, settle_amount, exchange_rate, notify_version, verify_sign, date_added, memo ) SELECT p.paypal_ipn_id, p.txn_type, p.reason_code, p.payment_type, p.payment_status, p.pending_reason, p.invoice, p.mc_currency, p.first_name, p.last_name, p.payer_business_name, p.address_name, p.address_street, p.address_city, p.address_state, p.address_zip, p.address_country, p.address_status, p.payer_email, p.payer_id, p.payer_status, p.payment_date, p.business, p.receiver_email, p.receiver_id, p.txn_id, po.num_cart_items, po.mc_gross, po.mc_fee, po.payment_gross, po.payment_fee, po.settle_amount, po.exchange_rate, p.notify_version, p.verify_sign, p.date_added, pm.memo FROM zen_(paypal_ipn p,zen_paypal_ipn_orders_memo pm LEFT JOIN zen_paypal_ipn_orders po ON p.paypal_ipn_id = po.paypal_ipn_id) WHERE p.paypal_ipn_id = pm.paypal_ipn_id;]

    I do not know where to go from here.

  3. #3
    Join Date
    Mar 2004
    Posts
    136
    Plugin Contributions
    0

    Default My v1.2 database cannot be upgraded using ZC_install

    Round and round in circles I go...

    I dumped the database.

    I found an old version of the database that I had backed up in February 2007. I imported it into the database using phpmyadmin. 92 Tables were added to the DB.

    When I look at the various tables in phpmyadmin, I see what I can only assume is valid data... customer names and addresses are there as well as what you'd expect to see in a backup of what was a perfectly functioning database under ZenCart v1.2

    I immediately went to my browser and went to /zc_install and attempted to upgrade the database. Once again, the sniffer suggested the following...

    Database Information -- Upgrade Sniffer predicts: upgrade v1.2.0 to v1.2.1
    Database Type = mysql
    Database Host = xxxx.xxxx.com
    Database Name = xxxxxx_new
    Database Username = xxxxxx_3
    Database Table-Prefix = zen_
    Database Privileges = Not Checked

    I deselected all the upgrade except for 1.2.0 to 1.2.1

    Ran upgrade and got the same blooming error


    1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(paypal_ipn p,zen_paypal_ipn_orders_memo pm LEFT JOIN zen_paypal_ipn_orders po O' at line 1
    in:
    [INSERT INTO zen_paypal (paypal_ipn_id, txn_type, reason_code, payment_type, payment_status, pending_reason, invoice, mc_currency, first_name, last_name, payer_business_name, address_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_id, payer_status, payment_date, business, receiver_email, receiver_id, txn_id, num_cart_items, mc_gross, mc_fee, payment_gross, payment_fee, settle_amount, exchange_rate, notify_version, verify_sign, date_added, memo ) SELECT p.paypal_ipn_id, p.txn_type, p.reason_code, p.payment_type, p.payment_status, p.pending_reason, p.invoice, p.mc_currency, p.first_name, p.last_name, p.payer_business_name, p.address_name, p.address_street, p.address_city, p.address_state, p.address_zip, p.address_country, p.address_status, p.payer_email, p.payer_id, p.payer_status, p.payment_date, p.business, p.receiver_email, p.receiver_id, p.txn_id, po.num_cart_items, po.mc_gross, po.mc_fee, po.payment_gross, po.payment_fee, po.settle_amount, po.exchange_rate, p.notify_version, p.verify_sign, p.date_added, pm.memo FROM zen_(paypal_ipn p,zen_paypal_ipn_orders_memo pm LEFT JOIN zen_paypal_ipn_orders po ON p.paypal_ipn_id = po.paypal_ipn_id) WHERE p.paypal_ipn_id = pm.paypal_ipn_id;]

  4. #4
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: Problem: mysql SQL Install file does not exist

    Argh -- it's the blasted "zen_" table-prefix.
    I still wish we'd never made that an option.

    Edit the /zc_install/sql/mysql_upgrade_zencart_120_to_121.sql file.
    Around line 165 you'll see this:
    Code:
    FROM (paypal_ipn p, paypal_ipn_orders_memo pm
    In your case, you'll have to manually insert your zen_ prefix for both table-names on just that line:
    Code:
    FROM (zen_paypal_ipn p, zen_paypal_ipn_orders_memo pm
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  5. #5
    Join Date
    Mar 2004
    Posts
    136
    Plugin Contributions
    0

    Default Re: Problem: mysql SQL Install file does not exist

    DrByte, I did as you outlined and got this error...

    1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(zen_paypal_ipn p,zen_zen_paypal_ipn_orders_memo pm LEFT JOIN zen_paypal_ipn_ord' at line 1
    in:
    [INSERT INTO zen_paypal (paypal_ipn_id, txn_type, reason_code, payment_type, payment_status, pending_reason, invoice, mc_currency, first_name, last_name, payer_business_name, address_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_id, payer_status, payment_date, business, receiver_email, receiver_id, txn_id, num_cart_items, mc_gross, mc_fee, payment_gross, payment_fee, settle_amount, exchange_rate, notify_version, verify_sign, date_added, memo ) SELECT p.paypal_ipn_id, p.txn_type, p.reason_code, p.payment_type, p.payment_status, p.pending_reason, p.invoice, p.mc_currency, p.first_name, p.last_name, p.payer_business_name, p.address_name, p.address_street, p.address_city, p.address_state, p.address_zip, p.address_country, p.address_status, p.payer_email, p.payer_id, p.payer_status, p.payment_date, p.business, p.receiver_email, p.receiver_id, p.txn_id, po.num_cart_items, po.mc_gross, po.mc_fee, po.payment_gross, po.payment_fee, po.settle_amount, po.exchange_rate, p.notify_version, p.verify_sign, p.date_added, pm.memo FROM zen_(zen_paypal_ipn p,zen_zen_paypal_ipn_orders_memo pm LEFT JOIN zen_paypal_ipn_orders po ON p.paypal_ipn_id = po.paypal_ipn_id) WHERE p.paypal_ipn_id = pm.paypal_ipn_id;]

  6. #6
    Join Date
    Mar 2004
    Posts
    136
    Plugin Contributions
    0

    Default Re: <SOLVED> My v1.2 database cannot be upgraded using ZC_install

    Well after numerous attempts at getting the old database upgraded to 1.3.7.1, I finally succeeded. Unfortunately I cannot say for sure why the previous attempts failed. But eventually the upgrade worked. I do think that DrByte's suggestion was most of the reason why it worked, but it didn't work the first three times I tried it after fixing the code he suggested needed fixing. In any case, it seems to be up and running now.

 

 

Similar Threads

  1. When running zc_install appears not to have upgraded
    By RAD1964 in forum Upgrading to 1.5.x
    Replies: 61
    Last Post: 13 Nov 2014, 11:54 PM
  2. Cannot access /zc_install dir
    By KeninNH in forum Installing on a Linux/Unix Server
    Replies: 1
    Last Post: 21 Jul 2010, 03:22 PM
  3. Cannot install Zencart 404 on zc_install
    By SteveCollins in forum Installing on a Linux/Unix Server
    Replies: 13
    Last Post: 13 May 2007, 06:46 PM
  4. Cannot run zc_install
    By stembo43 in forum Installing on a Linux/Unix Server
    Replies: 5
    Last Post: 10 Nov 2006, 04:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR