从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,
  `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.

dot_0620
dot_0620 是不是TimeStamp会自动更新系统时间和日期?IOW,你不应该传递任何值。
大约 4 年之前 回复
dpdt79577
dpdt79577 也许你需要UPDATE中的FROM_UNIXTIME();没什么特别的PHP。
接近 5 年之前 回复
dongmeng2687
dongmeng2687 进入函数的$inspection_date是什么?为什么你需要这个功能呢?
接近 5 年之前 回复
dozxos6346
dozxos6346 感谢您的建议..我将改变我的绑定
接近 5 年之前 回复
dongzhabo2796
dongzhabo2796 它没有提供更多。如果您想要或需要使用bindParam那么这就是所需要的。但是,对于大多数查询,bindValue更容易,并避免各种问题。原生mysqli中没有按值绑定。你必须通过param绑定。但是,PDO在使用bindValue时会为您生成变量。
接近 5 年之前 回复
dtr53557
dtr53557 如果处理非常大量的数据,bindParam提供速度。但是,对于大多数情况来说,这是不值得的。在您的情况下,您可以直接将数组中的值提供给execute语句,这会自动执行“bindValue”。它显着简化了代码。
接近 5 年之前 回复
dqcz57269
dqcz57269 这是否意味着我可以在绑定时写入字符串或使用函数的返回值?..如果是这样..如果bindValue提供bindParam所做的全部,那么为什么他们会创建一个bindParam:)
接近 5 年之前 回复
doutangtan6386
doutangtan6386 嗯,我可以建议你总是'bindValue'除非使用BLOB吗?此外,您的函数返回'sqldate'而不是格式为“U”的时间戳?
接近 5 年之前 回复

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

dongshan6870
dongshan6870 函数在执行时返回一个值。 但是,只有PHP知道该值的存储位置。 它可以在堆栈,工作区,CPU寄存器等等。无论如何,它不会告诉我们。 直接对结果进行“参考”是“有问题的”。 这可能是不可能的。 你可以把它存放在某个地方,这样你就知道它在哪里。 然后参考一下?
接近 5 年之前 回复
dq201401
dq201401 我知道这是一个问题,当试图绑定值,> bindParam(“:col”,123),因为bindparam通过引用传递,但我不清楚,直接传递一个函数不被视为一个引用。有人知道清楚, 起来。
接近 5 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐