phpBB2 by Przemo
Support forów phpBB2 modified by Przemo

FAQFAQ - PIERWSZA POMOC!!  regulaminREGULAMIN  SzukajSZUKAJ  UżytkownicyUżytkownicy  GrupyGrupy  StatystykiStatystyki
RejestracjaRejestracja  ZalogujZaloguj  DownloadDownload  katalog Forów DyskusyjnychKatalog Forów   FAQ Video tutoriale

Poprzedni temat «» Następny temat
Poprawka dla baz danych z sql_mode=only_full_group_by
Autor Wiadomość
Lorgan

Posty: 55
Wysłany: 23-07-2021, 10:43   Poprawka dla baz danych z sql_mode=only_full_group_by

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.
_________________
Zapraszam na moje forum.
 
     
Gadatliwa Kasia 

   
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Kopiowanie wszelkich treści zawartych na forum, modyfikacji oraz instrukcji bez zgody administracji i autorów tematów/postów zabronione!

Powered by phpBB modified by Przemo © 2003 phpBB
Strona wygenerowana w 0,06 sekundy. Zapytań do SQL: 11
Polecane serwisy



Najlepsze oprogramowanie do prowadzenia sklepu internetowegoNajlepszy program do sklepu firmowany przez Przem'a

Sklep z gadżetami

mediaclick.pl

agencja SEO Strategiczni.pl

Agencja SEO lepszymarketing.pl

Polisy Ubezpieczeniowe TU Europa

Design Cart

Margot pokoje na godziny Kraków

• Zamów reklamę