Forums / Upgrading to 1.5.x / Working on upgrade - SQL syntax error

Working on upgrade - SQL syntax error

Views: 5,460

Results 1 to 18 of 18
16 Jun 2014, 3:43 PM
#1
three_sisters avatar

three_sisters

Zen Follower

Join Date:
Nov 2006
Location:
Indiana
Posts:
188
Plugin Contributions:
0

Working on upgrade - SQL syntax error

I'm working on this upgrade. Trying to carefully follow directions, but I always get an error. (I say "always" because this is probably the 5th time I've started this project, dropped it, and came back to try again. This time I WILL finish. I've taken days off. It's scheduled. I'm committed. :yes:)

Here's what I've done:

1 - Created brand new data base for a test site.
2 - Created sub directory to install complete Zen 1.5.1 files into.
3 - Installed. Tested vanilla site. All works fine.
3 - Export my Zen 1.3.8a database and import it into the 1.5.1 (I assume the import replaces the 1.5.1 tables completely)

When it finishes I get the following error. Can someone tell me if this is important or can be ignored? Thanks a million!

SQL query:
<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" dir="ltr"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <link rel="icon" href="./favicon.ico" type="image/x-icon" /> <link rel="shortcut icon" href="./favicon.ico" type="image/x-icon" /> <title>phpMyAdmin</title> <link rel="stylesheet" type="text/css" href="phpmyadmin.css.php?server=1&token=32808b8103d1d67a14bdd123e09fd23b&js_frame=right&nocache=5552085656" /> <link rel="stylesheet" type="text/css" href="print.css" media="print" /> <link rel="stylesheet" type="text/css" href="./themes/pmahomme/jquery/jquery-ui-1.8.16.custom.css" /> <meta name="robots" content="noindex,nofollow" /> <script src="./js/cross_framing_protection.js?ts=1375007690" type="text/javascript"></script> <script src="./js/jquery/jquery-1.6.2fix-9521.js?ts=1399144421" type="text/javascript"></script> <script src="./js/jquery/jquery-ui-1.8.16.custom.js?ts=137500[...]
**MySQL said: **[IMG]https://box520.bluehost.com:2083/cpsess1810425142/3rdparty/phpMyAdmin/themes/dot.gif[/IMG]
#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 '<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="e' at line 1
16 Jun 2014, 4:02 PM
#2
rodg avatar

rodg

Deceased

Join Date:
Jan 2007
Location:
Australia
Posts:
6,263
Plugin Contributions:
4

Re: Working on upgrade - SQL syntax error

Three Sisters:

3 - Export my Zen 1.3.8a database and import it into the 1.5.1 (I assume the import replaces the 1.5.1 tables completely)

Yes, your assumption is correct (as a general rule), and it seems all you've done up to this point is also correct.

Three Sisters:

When it finishes I get the following error. Can someone tell me if this is important or can be ignored? Thanks a million!

That's a good question, but also a very strange error to get. In fact I've not seen one like it before, and I can't think of what would possibly cause it. Its as if the file you've exported has been corrupted or something.

When you did the export you didn't change the 'Format' setting from SQL to something else did you? (if you did, go back and do it again)

As to whether it can be ignored, this would depend on where in the exported .sql file this HTML portion resides. If it is at the very end of the .sql file it can be ignored. If it is anywhere else in the file it would mean you'll have an incomplete import.

An SQL file is really just a plain text file containing SQL commands, which means you can view/inspect it with a regular text editor.

Cheers
RodG

NOTE: We are sorry that Rod is no longer with us.
We are grateful for all his contributions to the Zen Cart community.

Ozpost - The Ultimate Shipping module for Australian Merchants. Click these links for its Homepage, or Download from Zen-Cart.com.

16 Jun 2014, 6:04 PM
#3
three_sisters avatar

three_sisters

Zen Follower

Join Date:
Nov 2006
Location:
Indiana
Posts:
188
Plugin Contributions:
0

Re: Working on upgrade - SQL syntax error

RodG:

When you did the export you didn't change the 'Format' setting from SQL to something else did you?

I changed no settings that I know of. I don't fully understand the database thing, so I just left all the default settings in place when I did both the export and the import.

RodG:

As to whether it can be ignored, this would depend on where in the exported .sql file this HTML portion resides. If it is at the very end of the .sql file it can be ignored. If it is anywhere else in the file it would mean you'll have an incomplete import.

Interesting... Because while I was waiting for some help/advice on this issue, I figured I'd try to upgrade the database and see what happened. Figured I could always import and start again. I got a message that the following 6 statements were ignored:

SKIPPED: Cannot add index idx_prod_id_orders_id_zen to table orders_products because it already exists.
SKIPPED: Cannot add index idx_cust_id_orders_id_zen to table orders because it already exists.
SKIPPED: Cannot drop index page_accessed on table admin_activity_log because it does not exist.
SKIPPED: Cannot drop index access_date on table admin_activity_log because it does not exist.
SKIPPED: Cannot add index idx_page_accessed_zen to table admin_activity_log because it already exists.
SKIPPED: Cannot add index idx_access_date_zen to table admin_activity_log because it already exists.

I have to wonder if these skipped statements will be a problem?

RodG:

An SQL file is really just a plain text file containing SQL commands, which means you can view/inspect it with a regular text editor.

I'll go try fishing around. Although I said I don't really understand what I'm doing - I really do enjoy this whole learning process! :)

16 Jun 2014, 6:20 PM
#4
three_sisters avatar

three_sisters

Zen Follower

Join Date:
Nov 2006
Location:
Indiana
Posts:
188
Plugin Contributions:
0

Re: Working on upgrade - SQL syntax error

OK - Found this. It runs on lines 44925 through 44945 of the SQL and there are a total of 45034. I guess that means it's not "at the very end"...

<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" dir="ltr"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <link rel="icon" href="./favicon.ico" type="image/x-icon" /> <link rel="shortcut icon" href="./favicon.ico" type="image/x-icon" /> <title>phpMyAdmin</title> <link rel="stylesheet" type="text/css" href="phpmyadmin.css.php?server=1&token=32808b8103d1d67a14bdd123e09fd23b&js_frame=right&nocache=5552085656" /> <link rel="stylesheet" type="text/css" href="print.css" media="print" /> <link rel="stylesheet" type="text/css" href="./themes/pmahomme/jquery/jquery-ui-1.8.16.custom.css" /> <meta name="robots" content="noindex,nofollow" /> <script src="./js/cross_framing_protection.js?ts=1375007690" type="text/javascript"></script> <script src="./js/jquery/jquery-1.6.2fix-9521.js?ts=1399144421" type="text/javascript"></script> <script src="./js/jquery/jquery-ui-1.8.16.custom.js?ts=1375007690" type="text/javascript"></script> <script src="./js/update-location.js?ts=1375007690" type="text/javascript"></script> <script src="./js/config.js?ts=1375007690" type="text/javascript"></script> <script src="./js/functions.js?ts=1375007690" type="text/javascript"></script> <script src="./js/jquery/jquery.qtip-1.0.0-rc3.js?ts=1375007690" type="text/javascript"></script> <script src="./js/messages.php?lang=en&db=threesis_zen&token=32808b8103d1d67a14bdd123e09fd23b" type="text/javascript"></script> <script src="./js/get_image.js.php?theme=pmahomme" type="text/javascript"></script> <script type="text/javascript">
16 Jun 2014, 9:54 PM
#5
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,513
Plugin Contributions:
176

Re: Working on upgrade - SQL syntax error

Three Sisters:

OK - Found this. It runs on lines 44925 through 44945 of the SQL and there are a total of 45034. I guess that means it's not "at the very end"...

<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" dir="ltr"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <link rel="icon" href="./favicon.ico" type="image/x-icon" /> <link rel="shortcut icon" href="./favicon.ico" type="image/x-icon" /> <title>phpMyAdmin</title> <link rel="stylesheet" type="text/css" href="phpmyadmin.css.php?server=1&token=32808b8103d1d67a14bdd123e09fd23b&js_frame=right&nocache=5552085656" /> <link rel="stylesheet" type="text/css" href="print.css" media="print" /> <link rel="stylesheet" type="text/css" href="./themes/pmahomme/jquery/jquery-ui-1.8.16.custom.css" /> <meta name="robots" content="noindex,nofollow" /> <script src="./js/cross_framing_protection.js?ts=1375007690" type="text/javascript"></script> <script src="./js/jquery/jquery-1.6.2fix-9521.js?ts=1399144421" type="text/javascript"></script> <script src="./js/jquery/jquery-ui-1.8.16.custom.js?ts=1375007690" type="text/javascript"></script> <script src="./js/update-location.js?ts=1375007690" type="text/javascript"></script> <script src="./js/config.js?ts=1375007690" type="text/javascript"></script> <script src="./js/functions.js?ts=1375007690" type="text/javascript"></script> <script src="./js/jquery/jquery.qtip-1.0.0-rc3.js?ts=1375007690" type="text/javascript"></script> <script src="./js/messages.php?lang=en&db=threesis_zen&token=32808b8103d1d67a14bdd123e09fd23b" type="text/javascript"></script> <script src="./js/get_image.js.php?theme=pmahomme" type="text/javascript"></script> <script type="text/javascript">

Not sure HOW that got into the .sql file, but it's clearly all HTML for phpMyAdmin to display its own pages in your browser.
It should NOT be in your .sql file. So ... how about removing that junk from the file, and any additional lines that don't belong ... and re-importing.

.
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.

16 Jun 2014, 9:55 PM
#6
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,513
Plugin Contributions:
176

Re: Working on upgrade - SQL syntax error

Three Sisters:

SKIPPED: Cannot add index idx_prod_id_orders_id_zen to table orders_products because it already exists.
SKIPPED: Cannot add index idx_cust_id_orders_id_zen to table orders because it already exists.
SKIPPED: Cannot drop index page_accessed on table admin_activity_log because it does not exist.
SKIPPED: Cannot drop index access_date on table admin_activity_log because it does not exist.
SKIPPED: Cannot add index idx_page_accessed_zen to table admin_activity_log because it already exists.
SKIPPED: Cannot add index idx_access_date_zen to table admin_activity_log because it already exists.

I have to wonder if these skipped statements will be a problem?
No problem. "cannot add because it already exists" or "cannot drop because it does not exist" are merely status messages saying that some things didn't need upgrading.

.
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.

16 Jun 2014, 10:25 PM
#7
three_sisters avatar

three_sisters

Zen Follower

Join Date:
Nov 2006
Location:
Indiana
Posts:
188
Plugin Contributions:
0

Re: Working on upgrade - SQL syntax error

Here is that section in its entirety. I noticed some of the lines mention ezpages and things relating to ZC. Could you verify that I should delete all of this since I'm not really sure what does or doesn't belong in the sql? (Although - I did look at the rest of the sql and NONE of it appears as this does.)

<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" dir="ltr"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <link rel="icon" href="./favicon.ico" type="image/x-icon" /> <link rel="shortcut icon" href="./favicon.ico" type="image/x-icon" /> <title>phpMyAdmin</title> <link rel="stylesheet" type="text/css" href="phpmyadmin.css.php?server=1&token=32808b8103d1d67a14bdd123e09fd23b&js_frame=right&nocache=5552085656" /> <link rel="stylesheet" type="text/css" href="print.css" media="print" /> <link rel="stylesheet" type="text/css" href="./themes/pmahomme/jquery/jquery-ui-1.8.16.custom.css" /> <meta name="robots" content="noindex,nofollow" /> <script src="./js/cross_framing_protection.js?ts=1375007690" type="text/javascript"></script> <script src="./js/jquery/jquery-1.6.2fix-9521.js?ts=1399144421" type="text/javascript"></script> <script src="./js/jquery/jquery-ui-1.8.16.custom.js?ts=1375007690" type="text/javascript"></script> <script src="./js/update-location.js?ts=1375007690" type="text/javascript"></script> <script src="./js/config.js?ts=1375007690" type="text/javascript"></script> <script src="./js/functions.js?ts=1375007690" type="text/javascript"></script> <script src="./js/jquery/jquery.qtip-1.0.0-rc3.js?ts=1375007690" type="text/javascript"></script> <script src="./js/messages.php?lang=en&db=database_name&token=32808b8103d1d67a14bdd123e09fd23b" type="text/javascript"></script> <script src="./js/get_image.js.php?theme=pmahomme" type="text/javascript"></script> <script type="text/javascript"> // <![CDATA[ if (typeof(parent.document) != 'undefined' && typeof(parent.document) != 'unknown' && typeof(parent.document.title) == 'string') { parent.document.title = 'box520.bluehost.com / localhost / database_name / ezpages | phpMyAdmin 3.5.8.2'; } // ]]> </script> <meta name="OBGZip" content="true" /> <!--[if IE 6]> <style type="text/css"> /* <![CDATA[ */ html { overflow-y: scroll; } /* ]]> */ </style> <![endif]--> </head>
<body>
        <div id="prefs_autoload" class="notice" style="display:none">
    <form action="prefs_manage.php" method="post">
        <input type="hidden" name="token" value="32808b8103d1d67a14bdd123e09fd23b" />
        <input type="hidden" name="json" value="" />
        <input type="hidden" name="submit_import" value="1" />
        <input type="hidden" name="return_url" value="export.php?" />
        Your browser has phpMyAdmin configuration for this domain. Would you like to import it for current session?            <br />
        <a href="#yes">Yes</a> / <a href="#no">No</a>
    </form>
</div>
<div id='floating_menubar'></div>
<div id='serverinfo'> <img src="themes/dot.gif" title="" alt="" class="icon ic_s_host item" /> <a href="main.php?token=32808b8103d1d67a14bdd123e09fd23b" class="item">localhost</a> <span class='separator item'> »</span> <img src="themes/dot.gif" title="" alt="" class="icon ic_s_db item" /> <a href="db_structure.php?db=database_name&token=32808b8103d1d67a14bdd123e09fd23b" class="item">database_name</a> <span class='separator item'> »</span> <img src="themes/dot.gif" title="" alt="" class="icon ic_s_tbl item" /> <a href="sql.php?db=database_name&table=ezpages&token=32808b8103d1d67a14bdd123e09fd23b" class="item">ezpages</a> <span class="hide" id="update_recent_tables"></span><div class="clearfloat"></div></div> <!-- PMA-SQL-ERROR --> <div class="error"><h1>Error</h1> <p><strong>SQL query:</strong> <a href="tbl_sql.php?sql_query=SHOW+TABLE+STATUS+FROM+%60database_name%60+LIKE+%27ezpages%27&show_query=1&db=database_name&table=ezpages&token=32808b8103d1d67a14bdd123e09fd23b"><span class="nowrap"><img src="themes/dot.gif" title="Edit" alt="Edit" class="icon ic_b_edit" /> Edit</span></a> </p> <p> <span class="syntax"><span class="inner_sql"><a href="./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fshow.html&token=32808b8103d1d67a14bdd123e09fd23b" target="mysql_doc"><span class="syntax_alpha syntax_alpha_reservedWord">SHOW</span></a> <span class="syntax_alpha syntax_alpha_reservedWord">TABLE</span> <span class="syntax_alpha syntax_alpha_reservedWord">STATUS</span> <span class="syntax_alpha syntax_alpha_reservedWord">FROM</span> <span class="syntax_quote syntax_quote_backtick">`database_name`</span> <a href="./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fstring-comparison-functions.html%23operator_like&token=32808b8103d1d67a14bdd123e09fd23b" target="mysql_doc"><span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span></a> <span class="syntax_quote syntax_quote_single">'ezpages'</span></span></span> </p> <p> <strong>MySQL said: </strong><a href="./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Ferror-messages-server.html&token=32808b8103d1d67a14bdd123e09fd23b" target="mysql_doc"><img src="themes/dot.gif" title="Documentation" alt="Documentation" class="icon ic_b_help" /></a> </p> <code> #2006 - MySQL server has gone away </code><br /> </div><script type="text/javascript"> //<![CDATA[ $(document).ready(function() { // updates current settings if (window.parent.setAll) { window.parent.setAll('en', 'utf8_general_ci', '1', 'database_name', 'ezpages', '32808b8103d1d67a14bdd123e09fd23b'); } // set current db, table and sql query in the querywindow if (window.parent.reload_querywindow) { window.parent.reload_querywindow( 'database_name', 'ezpages', ''); }

if (window.parent.frame_content) {
// reset content frame name, as querywindow needs to set a unique name
// before submitting form data, and navigation frame needs the original name
if (typeof(window.parent.frame_content.name) != 'undefined'
&& window.parent.frame_content.name != 'frame_content') {
window.parent.frame_content.name = 'frame_content';
}
if (typeof(window.parent.frame_content.id) != 'undefined'
&& window.parent.frame_content.id != 'frame_content') {
window.parent.frame_content.id = 'frame_content';
}
//window.parent.frame_content.setAttribute('name', 'frame_content');
//window.parent.frame_content.setAttribute('id', 'frame_content');
}
});

//]]>
</script>

</body> </html>

BTW - I replaced any reference to my database name with database-name. Don't know if that was necessary, but didn't want to find out it was after it was posted.

16 Jun 2014, 10:34 PM
#8
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,513
Plugin Contributions:
176

Re: Working on upgrade - SQL syntax error

It's as though you exported the browser's HTML, and not the .sql from the database.

Is there something you did differently than described here? http://www.zen-cart.com/content.php?144-how-can-i-do-a-site-backup-or-use-phpmyadmin-for-database-backup

.
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.

16 Jun 2014, 11:22 PM
#9
three_sisters avatar

three_sisters

Zen Follower

Join Date:
Nov 2006
Location:
Indiana
Posts:
188
Plugin Contributions:
0

Re: Working on upgrade - SQL syntax error

DrByte:

It's as though you exported the browser's HTML, and not the .sql from the database.

Grin. Well - it might look that way, but really, I exported the SQL from phpMyAdmin* almost *like the link you sent. I used quick instead of custom, but when I check custom all the settings were exactly as the link said they should be. I just exported it again perfectly and all that stuff is still at the end of the sql. The rest of the 45K+ lines look like I would expect them to.

17 Jun 2014, 12:02 AM
#10
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,513
Plugin Contributions:
176

Re: Working on upgrade - SQL syntax error

Puzzling.

Use a plain-text-editor and remove the junk from the file.

.
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.

17 Jun 2014, 3:11 AM
#11
three_sisters avatar

three_sisters

Zen Follower

Join Date:
Nov 2006
Location:
Indiana
Posts:
188
Plugin Contributions:
0

Re: Working on upgrade - SQL syntax error

OK - cleaned the junk away and the import went fine. No error message.

Did the upgrade and now there are no products or orders. The categories and customers are there, but no products or orders. Sigh. Any thoughts right off? Figured I ought to do some research first and then start a new topic for the new problem if I still need to.

17 Jun 2014, 8:26 AM
#12
rodg avatar

rodg

Deceased

Join Date:
Jan 2007
Location:
Australia
Posts:
6,263
Plugin Contributions:
4

Re: Working on upgrade - SQL syntax error

Three Sisters:

OK - cleaned the junk away and the import went fine. No error message.

Did the upgrade and now there are no products or orders. The categories and customers are there, but no products or orders. Sigh. Any thoughts right off? Figured I ought to do some research first and then start a new topic for the new problem if I still need to.

Since you've imported a V1.3.8 database into a V1.5.1 version of Zencart you'll now need to update the database.

  1. Run the http://www.your_domain_name.com/your_new_site**/zc_install**
  2. Choose the Upgrade Database option

Cheers
RodG

NOTE: We are sorry that Rod is no longer with us.
We are grateful for all his contributions to the Zen Cart community.

Ozpost - The Ultimate Shipping module for Australian Merchants. Click these links for its Homepage, or Download from Zen-Cart.com.

17 Jun 2014, 12:06 PM
#13
three_sisters avatar

three_sisters

Zen Follower

Join Date:
Nov 2006
Location:
Indiana
Posts:
188
Plugin Contributions:
0

Re: Working on upgrade - SQL syntax error

RodG:

....you'll now need to update the database...

Thanks, Rod, but I did the upgrade. And there are no products or orders. The last time I tried this I was missing things, too - I don't remember what.

I'm concluding that my backups must be loosing data somewhere. I opened the sql that I used for the import and tried to find the products that should have been there. No luck.

In a help file on my host's site (BlueHost) I saw that you shouldn't use phpMyAdmin to export/import if the database is over 50MB. So I went to check the size and the last export was 48.7. But what was even more interesting is I noticed that of 6 backups (all done within the last week) they vary in size from 32 MB to 48.7. So I brought them into a BeyondCompare and - they are all different - some drastically.

I found instruction on using SSH to backup databases larger than 50mb. But they just jump right into the command lines to enter. Can anyone tell me where and how to enter these command lines? I've search like crazy so I could avoid asking what must be a really basic question, but I can't find it.

Or - do you think I am off on my conclusion and I actually have other problems?

Thanks a million for your kind help!

17 Jun 2014, 1:05 PM
#14
rodg avatar

rodg

Deceased

Join Date:
Jan 2007
Location:
Australia
Posts:
6,263
Plugin Contributions:
4

Re: Working on upgrade - SQL syntax error

Three Sisters:

Thanks, Rod, but I did the upgrade. And there are no products or orders.

Although you apparently have another/different issue to resolve, please do keep in mind that you will need to repeat this database upgrade any time you've imported the V1.3.8 files.

Three Sisters:

I'm concluding that my backups must be loosing data somewhere. I opened the sql that I used for the import and tried to find the products that should have been there. No luck.

This could very well be related to all the 'junk text' that you've been reporting. (in fact I'd almost bet on it).
The 'junk text' being a symptom of the export issue, but not the cause.

Three Sisters:

In a help file on my host's site (BlueHost) I saw that you shouldn't use phpMyAdmin to export/import if the database is over 50MB. So I went to check the size and the last export was 48.7. But what was even more interesting is I noticed that of 6 backups (all done within the last week) they vary in size from 32 MB to 48.7. So I brought them into a BeyondCompare and - they are all different - some drastically.

It sounds like the server is timing out or aborting whilst exporting the data. All is not lost though.

Three Sisters:

I found instruction on using SSH to backup databases larger than 50mb. But they just jump right into the command lines to enter. Can anyone tell me where and how to enter these command lines? I've search like crazy so I could avoid asking what must be a really basic question, but I can't find it.

This is a rather drastic measure, that doesn't really assure success anyway. I suggest you forget about this for the time being.

Three Sisters:

Or - do you think I am off on my conclusion and I actually have other problems?
Thanks a million for your kind help!

It seems that you are on the right track, still, and I can think of two possible approaches for you to take from here.

The 1st thing I'd be trying is when you go to do the phpmyadmin export, select the 'Custom' options, and look for the dropdown menu for 'Compression'. By default this is set to 'None'. Change it to 'gzipped'. Whilst on this screen it is worthwhile selecting the option for 'Add Drop Table' (this will avoid the need to manually delete the 'old' tables when you go to import into the new server).

Click the 'Go' button, and wait. It could take a minute or three for the DB to be exported and compressed before it starts downloading to your computer.

The ~50Mb file will be reduced to around ~5-10Mb (at a guess) when it end up on your computer, and although it will have taken a little while to start, the download will be a lot quicker, and this will/should mitigate the timeout issues that I'm assuming is causing the problem.

Although you won't be able to view the contents of this file with a text editor you probably won't need to anyway. Simply import it 'as is' with phpmyadmin which will automatically decompress it for you.

I fully expect that you won't have any problems or errors.

If you do have any errors I'll go over the other approach with you.

Cheers
RodG

NOTE: We are sorry that Rod is no longer with us.
We are grateful for all his contributions to the Zen Cart community.

Ozpost - The Ultimate Shipping module for Australian Merchants. Click these links for its Homepage, or Download from Zen-Cart.com.

17 Jun 2014, 1:48 PM
#16
rodg avatar

rodg

Deceased

Join Date:
Jan 2007
Location:
Australia
Posts:
6,263
Plugin Contributions:
4

Re: Working on upgrade - SQL syntax error

Three Sisters:

Will try it, Rod! (Tho - must confess - I found the PuTTy thing and managed the mysqldump just before you posted. 'Twas fun to learn, but I'll try it your way now.)

PuTTy is a handy tool to have and learn. I always carry a copy of this on a USB stick for when I'm working on customers machines that don't have a SSH client installed and I need to access my home servers remotely.

If you do/did happen to overcome your problem using the SSH method, it is because SSH doesn't timeout in the same way that HTTP (web) does, so the 50Mb+ files are no longer a problem. However, I always go for the compression method 1st simply because it does reduce the filesize (and therefore the transfer time) quite significantly.

Cheers
RodG

NOTE: We are sorry that Rod is no longer with us.
We are grateful for all his contributions to the Zen Cart community.

Ozpost - The Ultimate Shipping module for Australian Merchants. Click these links for its Homepage, or Download from Zen-Cart.com.

17 Jun 2014, 4:00 PM
#17
three_sisters avatar

three_sisters

Zen Follower

Join Date:
Nov 2006
Location:
Indiana
Posts:
188
Plugin Contributions:
0

Re: Working on upgrade - SQL syntax error

And the good news is - It worked! "It" was several things, but it worked and all looks great now.

I tried and tried the phpMyAdmin thing and it timed out and gave me errors constantly.

I ended up using PuTTy to do a mysqldump to a gzip file - it was so fast and so easy. Then tried to import with phpMyAdmin. It stalled and stalled and errored. Finally, I switched browsers. And everything went perfectly smooth. So simple. I fought with this for years, honestly. This is where I have always thrown my hands up.

I love Firefox - most of the time. I am finding that there are a few things it chokes on and I can't get around it. So I switch browsers for those few things and all is fine.
*
Thanks again* for your help. Off to finish the customizing and add-on stuff. Then will re-do this whole database thing before finally going live, I hope. The rebuild really isn't that hard, but boy will I be glad when it's done. :)

18 Jun 2014, 9:09 AM
#18
rodg avatar

rodg

Deceased

Join Date:
Jan 2007
Location:
Australia
Posts:
6,263
Plugin Contributions:
4

Re: Working on upgrade - SQL syntax error

Three Sisters:

And the good news is - It worked! "It" was several things, but it worked and all looks great now.

Regardless how you solved the problem (Theres always more than one way to solve any given problem), Well done, and thanks for the update.

Cheers
RodG

NOTE: We are sorry that Rod is no longer with us.
We are grateful for all his contributions to the Zen Cart community.

Ozpost - The Ultimate Shipping module for Australian Merchants. Click these links for its Homepage, or Download from Zen-Cart.com.