2015-11-23 21:59

从mySQL切换到mariaDB timestamp messup


I have switched from MySQL to MariaDB which has caused some "minor" problems. One has been bugging me for hours now and i can't find the solution.

I moved my database by exporting it from MySQL and importing it into MariaDB which went well..

When one of my update queries did not work i narrowed it down to this function in my database handler:

public function updateEquipment($type,$product,$acquisition,$calibration_interval,$equipment_no,$inspection_date,$equipment_id,$active)
        $stmt = $this->conn->prepare("UPDATE equipment SET type = :type, acquisition = :acquisition, calibration_interval = :calibration_interval, equipment_no = :equipment_no, product = :product, inspection_date = :inspection_date, active = :active WHERE id = :equipment_id");

        $stmt->bindParam(":equipment_id", $equipment_id,PDO::PARAM_INT);
        $stmt->bindParam(":type", $type,PDO::PARAM_STR);
        $stmt->bindParam(":acquisition", $acquisition,PDO::PARAM_STR);
        $stmt->bindParam(":calibration_interval", $calibration_interval,PDO::PARAM_STR);
        $stmt->bindParam(":equipment_no", $equipment_no,PDO::PARAM_STR);
        $stmt->bindParam(":product", $product,PDO::PARAM_STR);
        $stmt->bindParam(":inspection_date", $this->formatDateStrToTimeStamp($inspection_date),PDO::PARAM_STR);
        $stmt->bindParam(":active", $active,PDO::PARAM_INT);
        return $stmt->execute();        

formatDateStrToTimeStamp function:

private function formatDateStrToTimeStamp($inspection_date)
        $day = substr($inspection_date,0,2);
        $month = substr($inspection_date,3,2);
        $year = substr($inspection_date,6,4);   
        return date('Y-m-d H:i:s', strtotime($year."-".$month."-".$day));

As you can see, i have switched out the binding of my inspection_date with a string representing the timestamp i want to update. I tested the statement WITHOUT updating my timestamp and then it was working as expected. As soon as i add the timestamp (in my case i have inserted a static timestamp) the row will NOT update and execute does not return (it should return true or false).

Heres my table structure:

CREATE TABLE `equipment` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` text NOT NULL,
  `acquisition` text NOT NULL,
  `calibration_interval` text NOT NULL,
  `equipment_no` text NOT NULL,
  `product` text NOT NULL,
  `active` int(11) NOT NULL DEFAULT '1'

Question: Are timestamps treated different in mariaDB, because i have not made any changes to my code since the switch, and i have simply imported my database from the export i made from my MySQL database.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答


  • dppxp79175 dppxp79175 6年前

    After debugging my pants off (because im not very good at debugging web applications) i finally found the answer to my problem.

    PDO's bindparam must bind a variable to a placeholder or questionmark which is also stated in the pdo documentation. In my case i tried both inserting a string directly when binding, and the original code with the error used the return value of a timestamp formater. In both cases i didn't use a variable when binding to my placeholder, hence the error....

    I came across the the error when i debugged the function using Chrome's Advanced Rest Client which revealed an error: "Only variables should be passed by reference".

    Solution 1:

    $inspect = $this->formatDateStrToTimeStamp($inspection_date);
    $stmt->bindParam(":inspection_date", $inspect,PDO::PARAM_STR);

    Solution 2:

    As pointed out by Ryan Vincent in the comments use bindValue instead (see his comment for further inspiration)

    But still a bit confused: I'm still a bit confused though, as the code previously ran on another host without problems. I cannot remember the PHP version or anything, but if someone could confirm that it was possible in previous version it would explain why...

    点赞 评论 复制链接分享