Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2019
    Location
    Toulouse France
    Posts
    9
    Plugin Contributions
    0

    database error $listing_sql is too long (over 1024 char and the query is not correct.

    [Note: remember to include site URL, ZC version, list of plugins, PHP version, etc ... read the Posting Tips shown above for information to include in your post here. And, remove this comment before actually posting!]
    Hello, i am quite green in PHP but learning .... as always.
    I noted a strange behavior in the query below and scratching my head to understand it.
    Then i copied the query in phpMyAdmin and noticed that the ". $and ." section was missing.
    Checked the string length and it is 1023 bytes !!!
    Then I taken out a couples of columns, reduce the length and ... YES it works fine.
    Here are the information:


    APACHE 2.4.7
    MYSQL 5.6.15
    PHP 5.5.8

    Zip file: zen-cart-v1.5.6c-07162019

    File: includes\index_filters\default_filter.php
    PHP Code:
    /**
     * default_filter.php  for index filters
     *
     * index filter for the default product type
     * show the products of a specified manufacturer
     *
     * @package productTypes
     * @copyright Copyright 2003-2019 Zen Cart Development Team
     * @copyright Portions Copyright 2003 osCommerce
     * @todo Need to add/fine-tune ability to override or insert entry-points on a per-product-type basis
     * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
     * @version $Id: Scott C Wilson 2019 Jun 23 Modified in v1.5.6c $
     */

    //
    //
    //
    line 53

    $listing_sql 
    "SELECT " $select_column_list " p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description,
                           IF(s.status = 1, s.specials_new_products_price, NULL) AS specials_new_products_price,
                           IF(s.status = 1, s.specials_new_products_price, p.products_price) AS final_price,
                           p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
                    FROM " 
    TABLE_PRODUCTS " p
                    LEFT JOIN " 
    TABLE_SPECIALS " s ON s.products_id = p.products_id
                    LEFT JOIN " 
    TABLE_PRODUCTS_DESCRIPTION " pd ON pd.products_id = p.products_id
                      AND pd.language_id = " 
    . (int)$_SESSION['languages_id'] . "
                    LEFT JOIN " 
    TABLE_PRODUCTS_TO_CATEGORIES " p2c ON p2c.products_id = p.products_id
                    LEFT JOIN " 
    TABLE_MANUFACTURERS " m ON m.manufacturers_id = p.manufacturers_id
                    WHERE p.products_status = 1
                    " 
    $and "
                    " 
    $alpha_sort
    Otherwise my system works.

    Keep me posted please.
    Claudio

  2. #2
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    9,103
    Plugin Contributions
    72

    Default Re: $listing_sql is too long (over 1024 char and the query is not correct.

    There are a couple of conditional sections (if/then/else) in the lines preceding that $listing_sql assignment that determine the values for the $and clause as well as the $alpha_sort. That's why the SELECT clause isn't directly pasteable into phpMyAdmin.

  3. #3
    Join Date
    Mar 2019
    Location
    Toulouse France
    Posts
    9
    Plugin Contributions
    0

    Default Re: $listing_sql is too long (over 1024 char and the query is not correct.

    Quote Originally Posted by lat9 View Post
    There are a couple of conditional sections (if/then/else) in the lines preceding that $listing_sql assignment that determine the values for the $and clause as well as the $alpha_sort. That's why the SELECT clause isn't directly pasteable into phpMyAdmin.
    Thank you for the answer, but i pasted the finished query, and that is why i realized that was not complete.
    I still thinking that the query is simply too long, i.e. >1024 char.

  4. #4
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    9,103
    Plugin Contributions
    72

    Default Re: $listing_sql is too long (over 1024 char and the query is not correct.

    Why do you think that the 1024 (1K) character length is an issue? If you copy/paste the following query into your site's phpMyAdmin, what is the current setting?

    Code:
    SHOW VARIABLES LIKE 'max_allowed_packet';

  5. #5
    Join Date
    Mar 2019
    Location
    Toulouse France
    Posts
    9
    Plugin Contributions
    0

    Default Re: $listing_sql is too long (over 1024 char and the query is not correct.

    Quote Originally Posted by lat9 View Post
    Why do you think that the 1024 (1K) character length is an issue? If you copy/paste the following query into your site's phpMyAdmin, what is the current setting?

    Code:
    SHOW VARIABLES LIKE 'max_allowed_packet';
    hi,
    This is it :
    SHOW VARIABLES LIKE 'max_allowed_packet'
    Profiling [ Inline ] [ Edit ] [ Create PHP Code ] [ Refresh ]



    + Options
    Variable_name Value
    max_allowed_packet 1048576
    ====
    To answer your question, i have shortened up the query - some fields where not of my interest, and it did worked bang on!

    As i said i am quite "green" in PHP / html but i have a long experience in C# and ASM (well this was long ago LOL), so my approach is try and correct. That correction worked but i would like to understand it.

    Question: "packet" means data sent or received or includes SQL queries as well?
    Thank you for your time.

  6. #6
    Join Date
    Jan 2004
    Posts
    65,292
    Blog Entries
    7
    Plugin Contributions
    228

    Default Re: $listing_sql is too long (over 1024 char and the query is not correct.

    Quote Originally Posted by Claudio_tls View Post
    SHOW VARIABLES LIKE 'max_allowed_packet'

    Variable_name Value
    max_allowed_packet 1048576
    MySQL until 5.6.5: default was 1048576 (1MB)
    From 5.6.6 until 8.0.2 default was 4194304 (4M)
    Since 8.0.3 default is 67108864 (64M)
    https://dev.mysql.com/doc/refman/8.0...allowed_packet

    MariaDB:
    16777216 (16M) >= MariaDB 10.2.4
    4194304 (4M) >= MariaDB 10.1.7
    1048576 (1MB) <= MariaDB 10.1.6
    https://mariadb.com/kb/en/library/se...allowed_packet

    You can safely increase your default setting to a more modern value.
    .

    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.

 

 

Similar Threads

  1. v151 Zen Cart v151 not sending the correct total amount over to Paypal
    By cjcoward in forum PayPal Express Checkout support
    Replies: 5
    Last Post: 5 Jan 2014, 07:15 PM
  2. Japanese Char not displaying properly on part of the webpages
    By gamenano in forum General Questions
    Replies: 2
    Last Post: 5 Nov 2012, 01:56 AM
  3. Replies: 0
    Last Post: 14 Sep 2009, 06:16 PM
  4. How to make the Long Title limit char. long?
    By explorer1979 in forum Templates, Stylesheets, Page Layout
    Replies: 11
    Last Post: 28 Aug 2009, 06:57 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