php - 创建表时事务在 Codeigniter 中不起作用

Question

任何人都可以帮助使用事务，我有在事务中创建表的查询，但它无法回滚并在 1 个查询出错时创建 2 个表。

这是我的 Controller :

 public function transaction_sample(){
    $this->data["title"] = get_class($this);

    /*
     * for public_transact_query in module library
     */

    //sample comet id
    $comet_id= $this->module->gen_password(9);


    //concatenate the get_camera_+comet_id
    $get_camera_name = "get_camera_".strtolower($comet_id);
    // adding table for get_camera
    $add_table_get_camera ="CREATE TABLE `".$get_camera_name."` (
                              `get_cam_key` varchar(16) DEFAULT NULL,
                              `get_cam_date` datetime DEFAULT NULL,
                              `get_cam_id` bigint(30) DEFAULT NULL,
                              `get_cam_cnt` int(5) DEFAULT NULL,
                              `get_cam_line` int(5) DEFAULT NULL,
                              `get_cam_loc` enum('door','aisle','back') DEFAULT NULL,
                              `get_cam_chunk` varchar(1024) DEFAULT NULL,
                              `get_cam_ip` varchar(16) DEFAULT NULL,
                              `get_cam_sysdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP
                            );";

    // concatenate the get_telemetry_+comet_id
    $get_telemetry_name = "get_telemetry_".strtolower($comet_id);
    // adding table for get_camera
    $add_table_get_telemetry = "CREATE TABLE `".$get_telemetry_name."` (
                                  `get_tel_datetime` datetime NOT NULL,
                                  `get_route_id` int(11) DEFAULT NULL,
                                  `get_drvr_id` int(11) DEFAULT NULL,
                                  `get_routestn_id` int(11) DEFAULT NULL,
                                  `get_tel_ip` varchar(16) DEFAULT NULL,
                                  `get_tel_vers` varchar(6) DEFAULT NULL,
                                  `get_tel_spd` int(4) DEFAULT NULL,
                                  `get_tel_lat` varchar(12) DEFAULT NULL,
                                  `get_tel_lng` varchar(12) DEFAULT NULL,
                                  `get_tel_batp` int(3) DEFAULT NULL,
                                  `get_tel_batv` int(3) DEFAULT NULL,
                                  `get_tel_bats` int(1) DEFAULT NULL,
                                  `get_tel_batc` int(3) DEFAULT NULL,
                                  `get_tel_volt` int(3) DEFAULT NULL,
                                  `get_tel_tito` int(1) DEFAULT NULL,
                                  `get_tel_chrgt` int(3) DEFAULT NULL,
                                  `get_tel_ctrlt` int(3) DEFAULT NULL,
                                  `get_tel_mtrt` int(3) DEFAULT NULL,
                                  `get_tel_sysdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP
                                );";

    // concatenate the get_tito_+comet_id
    //DROP TABLE IF EXISTS `".$get_tito_name."`;
    $get_tito_name = "get_tito_".strtolower($comet_id);
    //adding table for get_tito
    $add_table_get_tito="CREATE TABLE `".$get_tito_name."` (
                          `get_drvr_id` int(11) DEFAULT NULL,
                          `get_tito_key` varchar(16) DEFAULT NULL,
                          `get_tito_logdate` datetime DEFAULT NULL,
                          `get_tito_pair` varchar(16) DEFAULT NULL,
                          `get_tito_cardno` varchar(12) DEFAULT NULL,
                          `get_routestn_id` int(11) DEFAULT NULL,
                          `get_tito_load` decimal(9,2) DEFAULT '0.00',
                          `get_tito_fare` decimal(9,2) DEFAULT '0.00',
                          `get_tito_bal` decimal(9,2) DEFAULT '0.00',
                          `get_tito_type` int(1) DEFAULT NULL,
                          `get_tito_passcnt` int(2) DEFAULT NULL,
                          `get_tito_ip` varchar(16) DEFAULT NULL,
                          `get_tito_sysdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                        );";    
    $queryList  = array(
                    "query3" => $add_table_get_camera,
                    "query4" => $add_table_get_telemetry,
                    "query5" => $add_table_get_tito
                );
    $this->module->public_transact_query($queryList,"Successfully Added");
    $this->load->view("view",$this->data);
}   

我的图书馆:

    public function public_transact_query($queryList,$msg="Successfully Save/Add",$return_json=true){
    $this->ci->db->trans_begin();
    $isOk = false; 
    foreach ($queryList as $query => $query_value){
        $this->ci->db->query($query_value);
    }
    if($this->ci->db->trans_status() === FALSE){
        $msg =$this->ci->db->_error_message();
        $this->ci->db->trans_rollback();
        $isOk = false;
    }else{
        print_r($this->ci->db->trans_commit());
        //$isOk = true;
    }
    // if($return_json){
        // $this->my_return($isOk,$msg);
    // }else{
        // return $this->return_object($isOk,$msg);
    // }    
}

但它在更新、插入、删除sql语句时有效。提前谢谢

Answer 1

我已经完成了我的问题，这就是为什么创建表没有回滚

http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html