phpBB2 by Przemo

Lorgan · Posty: 55

Ostatnio host, na którym stoi moje forum zaktualizował wersję bazy, co doprowadziło do błędów tego typu:

Kod:

SQL Error : 1055 Expression #41 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'phpbb_forum.ph.post_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Niestety, nie mając konta z uprawnieniami admina w bazie, nie miałem możliwości permanentnej zmiany tego ustawienia, więc postanowiłem przerobić nieco kod forum, żeby radził sobie z tym na bieżąco. Pomyślałem, że komuś jeszcze może się to przydać moje chałturnicze obejście, stąd ten temat. Przetestowane na wersji 1.12.8.

Kod:

#
#-----[ OPEN ]------------------------------------------
#
config.php

#
#-----[ FIND ]------------------------------------------
#
$dbms = 'mysql4';

#
#-----[ REPLACE WITH]------------------------------------------
#
$dbms = 'mysqli';

#
#-----[ OPEN ]------------------------------------------
#
/includes/db.php

#
#-----[ FIND ]------------------------------------------
#
?>

#
#-----[ BEFORE, ADD ]---------------------------------------------
#
$db_mysqli = new mysqli($dbhost, $dbuser, $dbpasswd, $dbname);
$db_mysqli->set_charset("latin2");

#
#-----[ OPEN ]---------------------------------------------
#
warnings.php

#
#-----[ FIND ]------------------------------------------
#
include($phpbb_root_path . 'language/lang_' . $board_config['default_lang'] . '/lang_warnings.' . $phpEx);

#
#-----[ BEFORE, ADD ]---------------------------------------------
#
function mysqli_last_result($db_mysqli) {
while ($db_mysqli->more_results()) {
$db_mysqli->use_result();
$db_mysqli->next_result();
}
return $db_mysqli->store_result();
}

#
#-----[ FIND ]------------------------------------------
#
$sql = "SELECT w.*, u.username, u.user_gender, u.user_regdate, u.user_posts, u.user_from, u.user_email, u.user_website, u.user_jr, u.user_level, u.user_session_start, COUNT(w.id) as total, SUM(w.value) as value
FROM (" . WARNINGS_TABLE . " w, " . USERS_TABLE . " u)
WHERE w.userid = u.user_id
AND archive = '0'
GROUP by w.userid
ORDER BY $order_by";

if ( !($result = $db->sql_query($sql)) )

#
#-----[ REPLACE WITH]------------------------------------------
#
$sql = "SET @@sql_mode=REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''); ";
$sql .= "SELECT w.*, u.username, u.user_gender, u.user_regdate, u.user_posts, u.user_from, u.user_email, u.user_website, u.user_jr, u.user_level, u.user_session_start, COUNT(w.id) as total, SUM(w.value) as value
FROM (" . WARNINGS_TABLE . " w, " . USERS_TABLE . " u)
WHERE w.userid = u.user_id
AND archive = '0'
GROUP by w.userid
ORDER BY $order_by";

$db_mysqli->multi_query($sql);
if ( !($result = mysqli_last_result($db_mysqli)) )

#
#-----[ FIND ]------------------------------------------
#
$sql = "SELECT id FROM " . WARNINGS_TABLE . "
WHERE archive = '0'
GROUP by userid";
if ( !($result = $db->sql_query($sql)) )

#
#-----[ REPLACE WITH]------------------------------------------
#
$sql = "SET @@sql_mode=REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''); ";
$sql .= "SELECT id FROM " . WARNINGS_TABLE . "
WHERE archive = '0'
GROUP by userid";
$db_mysqli->multi_query($sql);
if ( !($result = mysqli_last_result($db_mysqli)) )

#
#-----[ OPEN ]---------------------------------------------
#
search.php

#
#-----[ FIND ]---------------------------------------------
#
include($phpbb_root_path . 'includes/functions_add.'.$phpEx);

#
#-----[ AFTER, ADD ]---------------------------------------------
#
function mysqli_last_result($db_mysqli) {
while ($db_mysqli->more_results()) {
$db_mysqli->use_result();
$db_mysqli->next_result();
}
return $db_mysqli->store_result();
}

#
#-----[ FIND ]---------------------------------------------
#
if ( $show_results == 'posts' )
{
$sql = "SELECT pt.post_text, pt.bbcode_uid, pt.post_subject, p.*, f.forum_id, f.forum_name, f.forum_color, f.forum_moderate, t.*, u.username, u.user_id, u.user_sig, u.user_sig_bbcode_uid, u.user_level, u.user_jr, u.user_allowhtml
FROM (" . FORUMS_TABLE . " f, " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TEXT_TABLE . " pt)
WHERE p.post_id IN ($search_results)
AND pt.post_id = p.post_id
AND f.forum_id = p.forum_id
AND p.topic_id = t.topic_id
AND p.poster_id = u.user_id
$no_password_forum";
}
else
{
if ( $board_config['post_overlib'] && $board_config['overlib'] && $userdata['overlib'] )
{
$post_text_select = ", pt.post_text, pt2.post_text as last_post_text";
$post_text_where = "AND pt.post_id = t.topic_first_post_id AND pt2.post_id = t.topic_last_post_id";
$posts_tables = ", " . POSTS_TEXT_TABLE . " pt, " . POSTS_TEXT_TABLE . " pt2";
}
else
{
$post_text_select = $post_text_where = $posts_tables = '';
}
$sql = "SELECT t.* $post_text_select, f.forum_id, f.forum_name, f.forum_color, f.forum_moderate, u.username, u.user_id, u.user_level, u.user_jr, u2.username as user2, u2.user_id as id2, u2.user_level as user_level2, u2.user_jr as user_jr2, p.post_username, p2.post_username AS post_username2, p2.post_time, ph.post_id as post_helped, p.post_approve, p2.post_approve as post_approve2
FROM (" . TOPICS_TABLE . " t, " . FORUMS_TABLE . " f, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2 $posts_tables)
LEFT JOIN " . POSTS_TABLE . " ph ON (t.topic_id = ph.topic_id AND ph.post_marked = 'y')
WHERE t.topic_id IN ($search_results)
AND t.topic_poster = u.user_id
AND f.forum_id = t.forum_id
AND p.post_id = t.topic_first_post_id
AND p2.post_id = t.topic_last_post_id
AND u2.user_id = p2.poster_id
$post_text_where
$no_password_forum
GROUP by t.topic_id";
}

#
#-----[ REPLACE WITH]------------------------------------------
#
$sql = "SET @@sql_mode=REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''); ";
if ( $show_results == 'posts' )
{
$sql .= "SELECT pt.post_text, pt.bbcode_uid, pt.post_subject, p.*, f.forum_id, f.forum_name, f.forum_color, f.forum_moderate, t.*, u.username, u.user_id, u.user_sig, u.user_sig_bbcode_uid, u.user_level, u.user_jr, u.user_allowhtml
FROM (" . FORUMS_TABLE . " f, " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TEXT_TABLE . " pt)
WHERE p.post_id IN ($search_results)
AND pt.post_id = p.post_id
AND f.forum_id = p.forum_id
AND p.topic_id = t.topic_id
AND p.poster_id = u.user_id
$no_password_forum";
}
else
{
if ( $board_config['post_overlib'] && $board_config['overlib'] && $userdata['overlib'] )
{
$post_text_select = ", pt.post_text, pt2.post_text as last_post_text";
$post_text_where = "AND pt.post_id = t.topic_first_post_id AND pt2.post_id = t.topic_last_post_id";
$posts_tables = ", " . POSTS_TEXT_TABLE . " pt, " . POSTS_TEXT_TABLE . " pt2";
}
else
{
$post_text_select = $post_text_where = $posts_tables = '';
}
$sql .= "SELECT t.* $post_text_select, f.forum_id, f.forum_name, f.forum_color, f.forum_moderate, u.username, u.user_id, u.user_level, u.user_jr, u2.username as user2, u2.user_id as id2, u2.user_level as user_level2, u2.user_jr as user_jr2, p.post_username, p2.post_username AS post_username2, p2.post_time, ph.post_id as post_helped, p.post_approve, p2.post_approve as post_approve2
FROM (" . TOPICS_TABLE . " t, " . FORUMS_TABLE . " f, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2 $posts_tables)
LEFT JOIN " . POSTS_TABLE . " ph ON (t.topic_id = ph.topic_id AND ph.post_marked = 'y')
WHERE t.topic_id IN ($search_results)
AND t.topic_poster = u.user_id
AND f.forum_id = t.forum_id
AND p.post_id = t.topic_first_post_id
AND p2.post_id = t.topic_last_post_id
AND u2.user_id = p2.poster_id
$post_text_where
$no_password_forum
GROUP by t.topic_id";
}

#
#-----[ FIND ]---------------------------------------------
#
if ( !$result = $db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Could not obtain search results', '', __LINE__, __FILE__, $sql);
}

$searchset = array();

#
#-----[ REPLACE WITH]------------------------------------------
#
$db_mysqli->multi_query($sql);
if ( !($result = mysqli_last_result($db_mysqli)) )
{
message_die(GENERAL_ERROR, 'Could not obtain search results', '', __LINE__, __FILE__, $sql);
}

$searchset = array();

#
#-----[ SAVE/CLOSE ALL FILES ]------------------------------------------
#

Pozdr.

Gadatliwa Kasia

Jane · Posty: 67

Miałam ten sam problem po aktualizacji do 1.12.8, a webd.pl niestety nie pozwala na wyłączenie tego mode. Poprawka Lorgana zadziałała. Dziękuję.

Podobny błąd odnośnie only_full_group_by wyrzuca również przy wchodzeniu w statystyki, ale one mało kiedy są kluczowe, więc tak tylko wspominam.