从mySQL切换到mariaDB timestamp messup

Question

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,
  `inspection_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` int(11) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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.

Answer 1

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...