Weirdly occational mySQL error code 1271

Question

I want to do a DB query that counts number of entries based on a mess of criteria. Here's what I have so far:

MySQL Error
Message: MySQL Query Error
SQL: SELECT COUNT(distinct p0.placeid) FROM uchome_place p0 INNER JOIN uchome_place_hours ph ON (p0.placeid=ph.placeid AND ph.weekdays=DATE_FORMAT(NOW(), "%w") AND ph.start<=TIME_FORMAT(TIMESTAMPADD(HOUR,2,NOW()), "%H:%i:%s") AND ph.end>=TIME_FORMAT( TIMESTAMPADD( MINUTE,(CASE p0.book_period WHEN 1 THEN 60 WHEN 2 THEN 120 WHEN 6 THEN 30 ELSE 15 END) ,NOW() ), "%H:%i:%s") )
Error: Illegal mix of collations for operation '>='
Errno.: 1271

This message is in response to me trying to feed the mySQL server with this query through PHP. However, when I try to feed the same mySQL server with the same query through phpMyAdmin, the query runs without errors as shown in this linked picture.

I did some further testing and found that the Illegal mix of collations comes from:

ph.end>=TIME_FORMAT( TIMESTAMPADD( MINUTE,(CASE book_period WHEN 1 THEN 60 WHEN 2 THEN 120 WHEN 6 THEN 30 ELSE 15 END) ,NOW() ), "%H:%i:%s")

How exactly can the PHP-fed query be regarded as Illegal mix of collations while the phpMyAdmin-fed query be regarded as runnable? I need the ph.end>=TIME_FORMAT( TIMESTAMPADD( MINUTE,(CASE book_period WHEN 1 THEN 60 WHEN 2 THEN 120 WHEN 6 THEN 30 ELSE 15 END) ,NOW() ), "%H:%i:%s") query be fed with PHP not phpMyAdmin so I need explanations on how the error occurs in PHP but not in phpMyAdmin, and how to fix this.

PS: the query in code block is already shortened as per Tim Biegeleisen's request. Original query is way longer, as shown in this linked picture.

PPS: Added additional materials requested by lbu. However, I fail to see what his point is.

<?php
class dbstuff {
    var $querynum = 0;
    var $link;
    var $charset;
    function connect($dbhost, $dbuser, $dbpw, $dbname = '', $pconnect = 0, $halt = TRUE) {
        if($pconnect) {
            if(!$this->link = @mysql_pconnect($dbhost, $dbuser, $dbpw)) {
                $halt && $this->halt('Can not connect to MySQL server');
            }
        }
        else
        {
            if(!$this->link = @mysql_connect($dbhost, $dbuser, $dbpw, 1)) {
                $halt && $this->halt('Can not connect to MySQL server');
            }
        }
        if($this->version() > '4.1')
        {
            if($this->charset) {
                @mysql_query("SET character_set_connection=".$this->charset.", character_set_results=".$this->charset.", character_set_client=binary", $this->link);
            }
            if($this->version() > '5.0.1') {
                @mysql_query("SET sql_mode=''", $this->link);
            }
        }
        if($dbname) {
            @mysql_select_db($dbname, $this->link);
        }
    }
    function query($sql, $type = '') {
        $func = ($type=='UNBUFFERED' && function_exists('mysql_unbuffered_query')) ?'mysql_unbuffered_query':'mysql_query';
        if(!($query = $func($sql, $this->link)) && $type != 'SILENT') {
            $this->halt('MySQL Query Error', $sql);
        }
        $this->querynum++;
        return $query;
    }
    function error() {
        return (($this->link) ? mysql_error($this->link) : mysql_error());
    }
    function errno() {
        return intval(($this->link) ? mysql_errno($this->link) : mysql_errno());
    }
    function fetch_row($query) {
        $query = mysql_fetch_row($query);
        return $query;
    }
    function version() {
        return mysql_get_server_info($this->link);
    }
    function close() {
        return mysql_close($this->link);
    }
    function halt($message = '', $sql = '') {
        $dberror = $this->error();
        $dberrno = $this->errno();
        echo "<div style=\"position:absolute;font-size:11px;font-family:verdana,arial;background:#EBEBEB;padding:0.5em;\">
            <b>MySQL Error</b><br>
            <b>Message</b>: $message<br>
            <b>SQL</b>: $sql<br>
            <b>Error</b>: $dberror<br>
            <b>Errno.</b>: $dberrno<br>
            </div>";
        exit();
    }
}
error_reporting(7);
set_magic_quotes_runtime(0);
$_SGLOBAL=array();
if(empty($_SGLOBAL['db'])){
    $_SGLOBAL['db']=new dbstuff;
    $_SGLOBAL['db']->charset='utf8mb4';
    $_SGLOBAL['db']->connect('localhost',$username,$password,$dbname);
}
$fullsql="SELECT COUNT(distinct p0.placeid) FROM uchome_place p0 INNER JOIN uchome_place_hours ph ON (p0.placeid=ph.placeid AND ph.weekdays=DATE_FORMAT(NOW(), \"%w\") AND ph.start<=TIME_FORMAT(TIMESTAMPADD(HOUR,2,NOW()), \"%H:%i:%s\") AND ph.end>=TIME_FORMAT( TIMESTAMPADD( MINUTE,(CASE book_period WHEN 1 THEN 60 WHEN 2 THEN 120 WHEN 6 THEN 30 ELSE 15 END) ,NOW() ), \"%H:%i:%s\") )";
$res=$_SGLOBAL['db']->query($fullsql);
if(false===$res)
{
    $place_list=false;
}
else
{
    $row=$_SGLOBAL['db']->fetch_row($res);
    if(false===$row)
    {
        $place_list='';
    }
    else
    {
        $place_list=$row[0];
    }
}
$_SGLOBAL['db']->close();
die(json_encode($place_list));
?>

I actually gave up editing your question because the query is so massive. You should give us a minimal example of the problem. Shorten the query as small as possible, while still having the same error. — Tim Biegeleisen, Feb 08 '17 at 04:44
Possible duplicate of [Troubleshooting "Illegal mix of collations" error in mysql](http://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql) — rkosegi, Feb 08 '17 at 06:48

score 0 · Accepted Answer · edited May 23 '17 at 12:02

Finally, I ended up solving this question myself. Thanks to rkosegi for hints.

Specifically, problem in my code was that the collation_connection of my DB was not explicitly defined. Which means the DB uses its default value, latin1, in the CASE statement's numbers. Since the column to be CASEd to, book_period, is collated in utf8_general_ci, this causes error 1271 for PHP-fed MySQL statements.

Solution code:

<?php
class dbstuff {
    var $querynum = 0;
    var $kink;
    var $charset;
    function connect($dbhost, $dbuser, $dbpw, $dbname, $halt=true){
        if(!$this->kink = @mysqli_connect($dbhost, $dbuser, $dbpw, $dbname)){
            $halt && $this->halt('Can not connect to MySQL server');
        }
        if($this->version() > '4.1')
        {
            if($this->charset) {
                @mysqli_query($this->kink,"SET character_set_connection=".$this->charset.",collation_connection=".$this->charset.", character_set_results=".$this->charset.", character_set_client=binary");
                #Question's faulty SQL below. Notice the missing collation_connection
                #mysqli_query($this->kink,"SET character_set_connection=".$this->charset.", character_set_results=".$this->charset.", character_set_client=binary");
            }
            if($this->version() > '5.0.1') {
                @mysql_query($this->kink,"SET sql_mode=''");
            }
        }
    }
...#rest of the code are unchanged
}
...
?>

Weirdly occational mySQL error code 1271

1 Answers1