You are here:  » sql error with mysql 5.7


sql error with mysql 5.7

Submitted by tobyhage on Sun, 2017-06-11 09:29 in

Hello,

My first post :-)

First i like to say that i like the tool very much! I have tested on two different hosting solutions. On one of the hosting solutions i have two issues.

The first one is that it is not possible to login in the backend. I already checked that cookie is set, but still not possible to login. Login form is asking for password again and again. Probably it is best to give you details about my site. Let me know...

The second issue i have is with pages from search, merchant, brand and category. I already did some research and found out that there is a problem with this query:

SELECT SQL_CALC_FOUND_ROWS id,COUNT(id) AS numMerchants,MIN(price) as minPrice FROM `products` WHERE merchant = 'Podobrace' GROUP BY search_name LIMIT 0,15

The following error is displayed in phpmyadmin.

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dev_websitedb-19426.pt123_products.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Submitted by support on Sun, 2017-06-11 10:15

Hi,

Welcome to the forum and thank you for your comments!

Regarding the login issue, this sounds like your server is behind a proxy that is presenting your connection with changing IP addresses. In 15/09A (and earlier), the IP address is included in the login cookie hash but this can be overridden easily by editing includes/admin.php - look for the following code at line 863:

    $remoteAddr = (isset($_SERVER["REMOTE_ADDR"])?$_SERVER["REMOTE_ADDR"]:"");

...and REPLACE with:

    $remoteAddr = "127.0.0.1";

If the problem is related to the latest distribution (16/10A), let me know and I'll check that out further with you.

Regarding the SQL error, this is down to a default SQL mode in your MySQL server configuration being incompatible with a particular query style used in Price Tapestry but this can be reset easily - edit includes/database.php and look for the following code at line 18:

      mysqli_set_charset($database_link,"utf8");

...and REPLACE with:

      mysqli_set_charset($database_link,"utf8");
      mysqli_query($database_link,"SET SESSION sql_mode=''");

(i'm going to include this reset in the next update)

Cheers,
David.
--
PriceTapestry.com

Submitted by sirmanu on Sun, 2017-06-11 11:52

Hi. Alternative to David's solution, you can edit my.cnf and add this line:

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Finally, reload or reset

Submitted by tobyhage on Sun, 2017-06-11 20:08

Hi,

Thank you for quick response. Database issue is solved now. Regarding the login issue, i'am using the latest version (16/10A). Let me know what you need from me :-)

Submitted by support on Mon, 2017-06-12 09:49

Hi Toby,

Regarding login, next thing to check (apologies I should have mentioned this initially) if you are using the $ symbol in $config_adminPassword it would need to be escaped by preceding with back-slash otherwise PHP would interpret it as a variable... for example:

  $config_adminPassword = "foo\$bar";

Cheers,
David.
--
PriceTapestry.com

Submitted by tobyhage on Mon, 2017-06-12 18:25

David,

I only have ! sign in password. But also not working with for example: test

Submitted by support on Mon, 2017-06-12 18:47

Hi Toby,

I'll follow up by email...

Cheers,
David.
--
PriceTapestry.com