typecho调用二级分类文章group by incompatible with sql_mode=only_full_group_by错误

楚天乐 512 0 条
分析

调用typecho二级分类文章列表显示data base query error

代码目录下搜索"Database Server Error",找到文件var/Typecho/Common.php 282行附近

var/Typecho/Common.php

if ($isException && $exception instanceof Typecho_Db_Exception) {
    $code = 500;
    @error_log($message);

    //覆盖原始错误信息
    $message = 'Database Server Error';

    if ($exception instanceof Typecho_Db_Adapter_Exception) {
        $code = 503;
        $message = 'Error establishing a database connection';
    } else if ($exception instanceof Typecho_Db_Query_Exception) {
        $message = 'Database Query Error';

                // 增加var_dump输出错误
        echo "<pre>";
        var_dump($exception);
        echo "</pre>";
    }
}   

好了,现在可以看到具体错误信息了。关键信息大约是这样

object(Typecho_Db_Query_Exception)#28 (7) {
  ["message":protected]=>
  string(303) "SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #21 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.nicai__typecho_relationships.mid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"
  ["string":"Exception":private]=>
  string(0) ""
  ["code":protected]=>
  string(5) "42000"
  ["file":protected]=>
  string(63) "xxxx/var/Typecho/Db/Adapter/Pdo.php"
  ["line":protected]=>
  int(105)
  ["trace":"Exception":private]=>
  array(7) {
    [0]=>
    array(6) {
      ["file"]=>
      string(51) "xxxx/var/Typecho/Db.php"
      ["line"]=>
      int(353)
      ["function"]=>
      string(5) "query"
      ["class"]=>
      string(22) "Typecho_Db_Adapter_Pdo"
      ["type"]=>
      string(2) "->"
      ["args"]=>
      array(4) {
        [0]=>
        string(562) "SELECT * FROM nicai__typecho_contents INNER JOIN nicai__typecho_relationships ON nicai__typecho_contents.`cid` = nicai__typecho_relationships.`cid`  WHERE  (nicai__typecho_contents.`status` = 'publish' OR
                        (nicai__typecho_contents.`status` = 'private' AND nicai__typecho_contents.`authorId` = '1') ) AND (nicai__typecho_contents.`created` < '1583034149' ) AND (nicai__typecho_relationships.`mid` IN ('29','30','31','8') ) AND (nicai__typecho_contents.`type` = 'post' ) GROUP BY nicai__typecho_contents.`cid`  ORDER BY nicai__typecho_contents.`created`  DESC LIMIT 10 OFFSET 0"

拷贝错误消息里的sql语句去数据库工具里执行,不要拷贝我的。。。

SELECT * FROM nicai__typecho_contents INNER JOIN nicai__typecho_relationships ON nicai__typecho_contents.`cid` = nicai__typecho_relationships.`cid`  WHERE  (nicai__typecho_contents.`status` = 'publish' OR
                        (nicai__typecho_contents.`status` = 'private' AND nicai__typecho_contents.`authorId` = '1') ) AND (nicai__typecho_contents.`created` < '1583034149' ) AND (nicai__typecho_relationships.`mid` IN ('29','30','31','8') ) AND (nicai__typecho_contents.`type` = 'post' ) GROUP BY nicai__typecho_contents.`cid`  ORDER BY nicai__typecho_contents.`created`  DESC LIMIT 10 OFFSET 0

打开var/Typecho/Common.php文件,280行附近

if ($isException && $exception instanceof Typecho_Db_Exception) {
            $code = 500;
            @error_log($message);

            //覆盖原始错误信息
            $message = 'Database Server Error';

            if ($exception instanceof Typecho_Db_Adapter_Exception) {
                $code = 503;
                $message = 'Error establishing a database connection';
            } else if ($exception instanceof Typecho_Db_Query_Exception) {
                $message = 'Database Query Error';
                echo "<pre>";
                var_dump($exception);
                echo "</pre>";
            }
        } else {

错误大约是这样

'mydb.nicai_typecho_relationships.mid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.016 sec
原因

mysql 5.7默认使用sql_mode=only_full_group_by模式

ONLY_FULL_GROUP_BY

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.
As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)
A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Before MySQL 5.7.5, enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. As of MySQL 5.7.5, this restriction is lifted so that the HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled.
解决方案

继续去看我们的前面的exception信息,可以找到查询数据库创建的位置

[3]=>
    array(6) {
      ["file"]=>
      string(55) "xxxx/var/Widget/Archive.php"
      ["line"]=>
      int(1391)
      ["function"]=>
      string(5) "query"
      ["class"]=>
      string(14) "Widget_Archive"
      ["type"]=>
      string(2) "->"
      ["args"]=>
      array(1) {

ok,我们去看var/Widget/Archive.php文件1301行附近(我本机代码修改过,请不要以我的行号为准)

/** 如果已经提前压入则直接返回 */
if ($hasPushed) {
    return;
}

/** 仅输出文章 */
$this->_countSql = clone $select;
$select->order('table.contents.created', Typecho_Db::SORT_DESC)
->page($this->_currentPage, $this->parameter->pageSize);
$this->query($select);

这就是关键了,只需要指定查询字段即可达到目的。把select语句稍作修改

// 添加->select('table.contents.*')
$select->select('table.contents.*')->order('table.contents.created', Typecho_Db::SORT_DESC)
        ->page($this->_currentPage, $this->parameter->pageSize);

最后记得把第一步中添加的var_dump语句去掉



发表我的评论
昵称 (必填)
邮箱 (必填)
网址