Page 1 of 2 12 LastLast
Results 1 to 10 of 18
  1. #1
    Join Date
    Nov 2006
    Location
    Indiana
    Posts
    192
    Plugin Contributions
    0

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

    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&amp;token=32808b8103d1d67a14bdd123e09fd23b&amp ;js_frame=right&amp;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:
    #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

  2. #2
    Join Date
    Jan 2007
    Location
    Australia
    Posts
    6,167
    Plugin Contributions
    7

    Default Re: Working on upgrade - SQL syntax error

    Quote Originally Posted by Three Sisters View Post
    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.

    Quote Originally Posted by Three Sisters View Post
    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

  3. #3
    Join Date
    Nov 2006
    Location
    Indiana
    Posts
    192
    Plugin Contributions
    0

    Default Re: Working on upgrade - SQL syntax error

    Quote Originally Posted by RodG View Post
    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.

    Quote Originally Posted by RodG View Post
    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?


    Quote Originally Posted by RodG View Post
    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! :)

  4. #4
    Join Date
    Nov 2006
    Location
    Indiana
    Posts
    192
    Plugin Contributions
    0

    Default 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&amp;token=32808b8103d1d67a14bdd123e09fd23b&amp ;js_frame=right&amp;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&amp;db=threesis_zen&amp;token=32808b8103d1d67a14bdd123e09fd 23b" type="text/javascript"></script>
    <script src="./js/get_image.js.php?theme=pmahomme" type="text/javascript"></script>
    <script type="text/javascript">

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

    Default Re: Working on upgrade - SQL syntax error

    Quote Originally Posted by Three Sisters View Post
    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&amp;token=32808b8103d1d67a14bdd123e09fd23b&amp ;js_frame=right&amp;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&amp;db=threesis_zen&amp;token=32808b8103d1d67a14bdd123e09fd 23b" 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.

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

    Default Re: Working on upgrade - SQL syntax error

    Quote Originally Posted by Three Sisters View Post
    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.

  7. #7
    Join Date
    Nov 2006
    Location
    Indiana
    Posts
    192
    Plugin Contributions
    0

    Default 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&amp;token=32808b8103d1d67a14bdd123e09fd23b&amp ;js_frame=right&amp;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&amp;db=database_name&amp;token=32808b8103d1d67a14bdd123e09f d23b" 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'>&nbsp;»</span>
    <img src="themes/dot.gif" title="" alt="" class="icon ic_s_db item" />
    <a href="db_structure.php?db=database_name&amp;token=32808b8103d1d67a14bdd123e09fd2 3b" class="item">database_name</a>
    <span class='separator item'>&nbsp;»</span>
    <img src="themes/dot.gif" title="" alt="" class="icon ic_s_tbl item" />
    <a href="sql.php?db=database_name&amp;table=ezpages&amp;token=32808b8103d1d67a14bdd 123e09fd23b" 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+%27e zpages%27&amp;show_query=1&amp;db=database_name&amp;table=ezpages&amp;token=3280 8b8103d1d67a14bdd123e09fd23b"><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&amp ;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&amp;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&amp;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.

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

    Default 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?...atabase-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.

  9. #9
    Join Date
    Nov 2006
    Location
    Indiana
    Posts
    192
    Plugin Contributions
    0

    Default Re: Working on upgrade - SQL syntax error

    Quote Originally Posted by DrByte View Post
    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.

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

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

 

 
Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 1
    Last Post: 25 Aug 2011, 05:58 AM
  2. Advance Search Results SQL syntax error after 1.3.9b upgrade
    By chrisruggia in forum Upgrading from 1.3.x to 1.3.9
    Replies: 3
    Last Post: 13 May 2010, 01:34 PM
  3. MySQL Error 1064 You have an error in your SQL syntax;
    By dmm2020 in forum General Questions
    Replies: 2
    Last Post: 22 Feb 2009, 05:02 AM
  4. error message 1064 you have an error in your sql syntax
    By ngsl in forum General Questions
    Replies: 0
    Last Post: 22 Aug 2008, 06:44 AM
  5. SQL Syntax error: check for right syntax to use near '000000
    By gabyone in forum General Questions
    Replies: 2
    Last Post: 9 Feb 2008, 08:23 PM

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