Upgrade problem to 2.0.17 with mysql

Started by kasparh, May 15, 2020, 11:48:37 AM

Previous topic - Next topic

kasparh

Hi to all, i upgraded SMF to version 2.0.17, but now the forum is not more accessible, the home page is ok, but when i try to navigate the forum i get this error:

Expression #30 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'salsamer_salsamerende.big.id_attach' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
File: /home/salsamer/public_html/salsamerende/Sources/MessageIndex.php

I already understand the problem is the mysql upgrade from the provider to version 5.0.17 and i've already opened a ticket to try to disable sql_mode=only_full_group_by , but they reply me this will impact all other users, so i need to find a solution not involving the full config of mysql.
Anyone can help me ?

shawnb61

What version & engine of mysql are you running?

There are some known issues with MariaDB that might have to be coded around...
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

kasparh

Localhost via UNIX socket

5.5.54-cll-lve - MySQL Community Server (GPL)

vbgamer45

Might require a code change edit your Settings.php and put


$mysql_set_mode = 1;
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

kasparh

Quote from: vbgamer45 on May 15, 2020, 01:34:01 PM
Might require a code change edit your Settings.php and put


$mysql_set_mode = 1;


nope, i tried to insert the code :

Quote$db_type = 'mysql';
$db_server = 'localhost';
$db_name = 'xxxxx';
$db_user = 'xxxx';
$db_passwd = 'xxxx';
$ssi_db_user = '';
$ssi_db_passwd = '';
$db_prefix = 'xxx';
$db_persist = 0;
$db_error_send = 0;
$mysql_set_mode = 1;

but no results, the error is still the same

vbgamer45

Opps actually needs to be

$mysql_set_mode = true;
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

kasparh

Quote from: vbgamer45 on May 15, 2020, 01:45:05 PM
Opps actually needs to be

$mysql_set_mode = true;


still no results:

Quote$db_type = 'mysql';
$db_server = 'localhost';
$ssi_db_user = '';
$ssi_db_passwd = '';
$db_persist = 0;
$db_error_send = 0;
$mysql_set_mode = true;

QuoteExpression #30 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxxxxx.big.id_attach' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
File: /home/salsamer/public_html/xxxxxx/Sources/MessageIndex.php
Riga: 421

kasparh

I think there is something wrong with messaggeindex.php.
If from the home page of the forum i jump directly to a post, for example :

http://forum.domain.com/index.php?topic=11023.msg102766;boardseen#new

i'm able to read the post, but if i try to reply i get the following error:

QuoteField 'edit_reason' doesn't have a default value
File: /home/xxxx/public_html/xxxxxx/Sources/Subs-Post.php
Riga: 1835

But if from the home i try to navigate the forum's sections, i have the error from which i started the 3d

i can't undestrand if it's a php problem or a db problem.
raw 421 is a closure " ); " and raw 1835 is empty...

vbgamer45

edit_reason is a mod issue probably you need to change the column in the database to have a default value it's structure.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

kasparh

Quote from: vbgamer45 on May 15, 2020, 03:31:25 PM
edit_reason is a mod issue probably you need to change the column in the database to have a default value it's structure.
the default value was "none", i changed it to "NULL" but still have the same error, tried to change to a default custom value but the db told me "BLOB field can't have a default value". It seems a nightmare  :-[ :'(

vbgamer45

blob fields normally not used in SMF...
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

kasparh

was the message from php, the field is tinytext i tried to change it to text changing the default value to a string and also to timestamp, changing the default value to current timestamp, no error from the query on db, but when i try to submit a post, there's always the same error

"Field 'edit_reason' doesn't have a default value"

vbgamer45

You can change tinytext to varchar(255)
edit_reason seems to be part of a mod though
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

kasparh

Quote from: vbgamer45 on May 15, 2020, 04:20:40 PM
You can change tinytext to varchar(255)
edit_reason seems to be part of a mod though

tried to change to varchar with 255 char, tried to set NULL as defaultand to put a custom value, same problems.
I think the mod is this one

https://custom.simplemachines.org/mods/index.php?mod=349

but there is no a procedure to uninstall it  :'(

Kindred

in phpmyadmin run this


ALTER TABLE smf_messages ALTER edit_reason SET DEFAULT NULL;
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

kasparh

Quote from: Kindred on May 15, 2020, 04:48:38 PM
in phpmyadmin run this


ALTER TABLE smf_messages ALTER edit_reason SET DEFAULT NULL;

still no results, i think i need to remove manually the mod. But the main problem is the

Expression #30 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxxxxxx.big.id_attach' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
File: /home/xxxxx/public_html/xxxxxx/Sources/MessageIndex.php

and the hosting provider told me this is a config that will affects all the customers and can not be done

kasparh

my provider disabled the variable and now the forum is running, the problem was new version of mysql

Advertisement: