PHP/ADODB - can't rollback MySQL transaction



  • So, there are my codez:

    <?php
    include_once '../../../../include/adodb/adodb.inc.php';
    
    class loggingConnection {
    	
    	private $innerConnection;
    	
    	function loggingConnection()
    	{
    		$this->innerConnection = ADONewConnection('mysqlt');
    		$this->innerConnection->PConnect('localhost', 'redacted', 'redacted', 'redacted');
    	}
    	
    	function __call($method, $args)
    	{
    		echo "Called DB method : ".$method."\r\n";
    		echo "Arguments: \r\n";
    		@print_r($args);
    		echo "\r\n";
    		$result = call_user_func_array(array($this->innerConnection, $method), $args);
    		echo "SQL: \r\n";
    		@print_r($result->sql);
    		echo "\r\n";
    		echo "Results: \r\n";
    		@print_r($result->fields);
    		echo "\r\n";
    		return $result;
    	}	
    }
    
    global $conn;
    $conn = new loggingConnection();
    
    echo "BeginTrans() returns" . $conn->BeginTrans() . "\r\n";
    $conn->Execute("INSERT INTO some_table (some_column, other_column) VALUES (?, ?)", array('1', '2'));
    $conn->Execute("SELECT * FROM some_table");
    echo "RollbackTrans() returns" . $conn->RollbackTrans() . "\r\n";
    $conn->Execute("SELECT * FROM some_table");
    ?>
    

    Basically, it's a script to test what's ultimately another class, dumping all ADODB activity. Don't know how much is relevant - you can probably replace $conn with a regular ADODB object.

    The issue is, after running $conn->RollbackTrans() the transaction does not rollback as intended, despite RollbackTrans() returning true. I've changed the table from InnoDB to MyISAM, I've tried mysqli, mysqlt and mysql drivers, I've tried Begin/Fail/CompleteTrans() path - to no avail, the record I've inserted is still there after I run the last SELECT, and it's there when browsing the table with PHPMyAdmin. I'll delete it there, run the code again, and it still doesn't work as intended.

    I'm out of ideas at this point - got some?


    Filed under: and no, I'm not doing this PHP shit for fun



  • MyISAM doesn't support transactions, but InnoDB does. Make sure you switch back to that engine.



  • @Keith said:

    MyISAM doesn't support transactions, but InnoDB does.

    Oh fuck me, I got it backwards, didn't I.



  • Yep, I did. I had the table at InnoDB initially, and probably an otherwise borked code, so I went to Google and somehow got from it that I need MyISAM. Then I kept trying to fix my code, because well, I have the engine part right, right?

    As a side note, WHY THE FUCK IS AN ENGINE THAT DOESN'T SUPPORT TRANSACTIONS EVEN A THING.



  • @Maciejasjmj said:

    As a side note, WHY THE FUCK IS AN ENGINE THAT DOESN'T SUPPORT TRANSACTIONS EVEN A THING.

    For the people who want to use MySQL as a speedy NoSQL replacement. Eg shoving in a bunch of logs or something.



  • Don't most NoSQL databases have either transactions or at least some sort of atomic update functionality?



  • @ben_lubar said:

    Don't most NoSQL databases have either transactions or at least some sort of atomic update functionality?

    Yes.

    Your point?



  • Do I really need one?



  • *Shrugs*. If you already have an MySQL setup and just need a little addon, it's a no-brainer. Even if not, MySQL is a mature stable DB, with great tooling. Lots of people know how to program it, tune it and fix it. It's hosted everywhere. That's enough upsides to make it a contender, at least.



  • MySQL claims to support ROLLBACK START TRANSACTION, COMMIT, and ROLLBACK Syntax. Although I am not too sure how version dependant it is. I personally have yet to be in a situation where I have needed to use ROLLBACK, there have been times when I have considered it. In my current role "...the DB Engine will be MyISAM..." for various reasons that individually would cause the the entire community to scream :wtf:. But that not the point here.

    Also, @Maciejasjmj, thank you for including your test class. My only concern would be your creation of a Connection within the class (I have no idea what Pconnect does), rather than passing one into it. I am not even sure which MySQL API you are using - Using 'mysqlt' and ADODB I found adodb-mysqlt.inc.php. Which seems to be using mysql rather than mysqli (not that makes any difference) but when you connect using mysql_connect() the resource returned becomes a sort of superglobal and as such you don't need to specifiy it.The fun comes when you create another one, and the question then becomes "which one gets used". Please see mysql_query. I offer the link only for your convenience as list time I cut and pasted, Discouse thought it needed reformatting and it took ages sorting it out.

    P.s. I notice in the inc file that it is "doing something" about ROLLBACK because, apparently MySQL has issues with it



  • Sorry, forgot to mention: Can you rollback with different connections to the DB? - I have no idea if this is possible or relevant.



  • Eh, it's just some scratch code before I integrate the code with the rest of the application, so that I can test the thing standalone. The application itself populates $conn in some other way I'm not going to be terribly concerned with unless something breaks after integration.

    Finally, I suppose we'll have a single connection, so it doesn't matter much. But thanks for the tips anyway.


Log in to reply