Bug in PDO? Table disappears after INSERT command, how to debug?

Question

Introducing

I’m creating a library with some "magic methods" on top of PDO, already I did it with Mysqli and worked beautifully. The intention is to use in a Framework I am building, finally no more jabá.

Problem

Mysteriously my test table is being deleted right after (or during, I’m not sure) INSERT.

Code

Filing cabinet: php test.

 <?php

     $con = New ConnectionPDO;

     echo '<pre>';
     var_dump( $con->getTables() );
     echo '</pre>';

     $r = $con->insert('tab_teste',Array('id'=>1,'name' => 'First Record', 'col3' => 'test '))->execute();
     $log .= ($r ? 'Success' : 'Fail') . PHP_EOL;

     echo '<pre>';
     echo $con->lastSQL() . PHP_EOL;
     echo '</pre>';

     echo '<pre>';
     var_dump( $con->getTables() );
     echo '</pre>';
 ?>

Class: Connectionpdo

 <?php

 class ConnectionPDO extends PDO {

    function __construct($dsn, $username = NULL, $password = NULL, $options = NULL) {
       parent::__construct($dsn, $username, $password, $options);
       $this->LoadDriverMethods();
    }

    private function LoadDriverMethods(){
       $driver = __DIR__ . DIRECTORY_SEPARATOR . 'drivers' . 
                           DIRECTORY_SEPARATOR . 'sqldriver.' . 
                           strtolower($this->getAttribute(PDO::ATTR_DRIVER_NAME)) . '.php';

       if (!is_file($driver))
          throw new Exception('Não foi possível carregar os métodos do driver', 1);

       require_once $driver;
       $this->driver = new SQLDriver();
    }

    public function insert($table, $data) {
       $this->lastSQL = $this->driver->insert($table, $data);

       $stmt = $this->prepare($this->lastSQL);

       $this->driver->setParams($this->stmt);

       $this->log .= $this->driver->flushLog();

       return $this->stmt;
    }

 }
 ?>

Class: Sqldriver

 <?php
 class SQLDriver implements DriverInterface {

    public function insert($table, $data){

       $this->clearParams();

       $sql = "INSERT INTO `{$table}` ";

       $colunms = Array();

       $values  = Array();

       foreach ($data as $col => $value) {
          $colunms[] = "`{$col}`";
          $values[]  = '?';

          $this->addParam($col, $value);
       }

       $sql .= '(' . implode(', ', $colunms) . ') VALUES (' . implode(', ', $values) . ');';

       $this->log .= $sql . PHP_EOL;

       return $sql;
    }

    public function addParam($key, $value){
       //$this->params[':'.$key] = $value;
       $this->params[] = $value;
    }

    public function setParams(PDOStatement $stmt){
       $params = $this->getParams();
       $this->log .=  'Setando Parâmetros: '.PHP_EOL;
       if (is_array($params) && !empty($params)){
          foreach ($params as $param => $value){
             $stmt->bindValue($param+1, $this->prepareParam($value), $this->getParamType($value));
             $this->log .=  $param+1 . ' => ' . $this->prepareParam($value) . PHP_EOL;
          }
       }
       $this->log .= PHP_EOL.'-----------------------------'.PHP_EOL.PHP_EOL;
    }

 }
 ?>

I just put the code around the error. The complete code can be seen in the links:

• Test file.php
• Connectionpdo class
• Sqldriver class

Unless someone downloads the code and keeps testing and searching I don’t think you’ll find the problem so easily, then the main question here is, how to debug the PDO? High school is, what might be causing this? The third, why?

Answer 1

Part of the problem is the example of the question it does not simulate the symptom of the table disappear, already the github makes clear.

TL;DR

The method insert(), the select also, prepares the last sql statement but returns the previous prepared query ie the combination of the test DROP TABLE is executed twice.

---

I set up a smaller test, I just made a change to highlight the problem, I changed the visibility $stmt in ConnectionPDO from private to public only for testing, so it is possible to see which was the last query executed through queryString and make the comparison.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
$stmt = NULL;
function __autoload($class){
   if (class_exists($class)) return true;
   $dir = __DIR__.DIRECTORY_SEPARATOR;
   $ext = '.php';
   if (file_exists($dir.$class.$ext)) require_once $dir.$class.$ext;
   else exit('Couldn\'t open class '.$class.'!');
}
$settings = Array(
   'driver'    => 'mysql',
   'host'      => 'localhost',
   'port'      => '3306',
   'schema'    => 'pdoweb2',
   'username'  => 'root',
   'password'  => 'root'
);
$dns = $settings['driver'] . ':host=' . $settings['host'] . 
                             ';port=' . $settings['port'] . 
                             ';dbname=' . $settings['schema'];
$con = new ConnectionPDO($dns, $settings['username'], $settings['password']);



$r = $con->drop('tab_teste');

    $fields = Array('id' => Array('type' => 'int', 'size' => '4', 'comment' => 'first key', 'auto' => true),
                    'name' => Array('type' => 'varchar', 'size' => '60', 'comment' => 'test name'),
                    'col3' => Array('type' => 'varchar', 'size' => '60', 'default' => NULL,'comment' => 'test name')
            );

    $sql =  'CREATE TABLE `tab_teste` (' . PHP_EOL .
            '   id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,' . PHP_EOL .
            '   name VARCHAR(60),' . PHP_EOL .
            '   col3 VARCHAR(60) DEFAULT NULL' . PHP_EOL .
            ');';

    $r = $con->query($sql);

    echo 'getTables()<pre>';        
    print_r($con->getTables());
    echo '</pre>';

    $r = $con->insert('tab_teste',Array('id'=>1,'name' => 'First Record', 'col3' => 'test '))->execute();

    echo 'LastSQL: '. $con->lastSQL() .'<br>';
    echo 'Ultima consulta preparada: '. $con->stmt->queryString .'<br><br>';


    $r = $con->insert('tab_teste',Array('id'=>2,'name' => 'Second Record', 'col3' => 'test '))->execute();
    echo 'LastSQL: '. $con->lastSQL() .'<br>';
    echo 'Ultima consulta preparada: '. $con->stmt->queryString .'<br><br>';


    echo 'getTables()<pre>';        
    print_r($con->getTables());
    echo '</pre>';

The exit was:

getTables()

Array
(
    [0] => tab_teste
)

LastSQL: INSERT INTO `tab_teste` (`id`, `name`, `col3`) VALUES (?, ?, ?);
Ultima consulta preparada: DROP TABLE IF EXISTS `tab_teste`;

LastSQL: INSERT INTO `tab_teste` (`id`, `name`, `col3`) VALUES (?, ?, ?);
Ultima consulta preparada: DROP TABLE IF EXISTS `tab_teste`;

getTables()

Array
(
)

The first instruction executed is a drop() no problem occurs, the next is a insert() here is where the problem happens, the Insert is prepared and played in a local variable $stmt and a few lines later is returned the mémbro $this->stmt that has previously processed the drop.

public function insert($table, $data) {
   $this->lastSQL = $this->driver->insert($table, $data);
   $stmt = $this->prepare($this->lastSQL); //<--- variável local
   $this->driver->setParams($this->stmt);
   $this->log .= $this->driver->flushLog();
   return $this->stmt; //<--- retorno da consulta anterior, o drop no caso
}

public function drop($table){
   $this->lastSQL = $this->driver->drop($table);
   $this->stmt = $this->prepare($this->lastSQL);
   $this->log .= $this->driver->flushLog();
   return $this->stmt;
}

To fix this make the assignment in the meson instead of the local variable, apply the change in the insert() and select().

Change:

$stmt = $this->prepare($this->lastSQL);

To:

$this->stmt = $this->prepare($this->lastSQL);