Transaction with PHP & MySQL, foreign key fails on LAST_INSERT_ID()?

[mslilafowler]

Hi everyone

I need your help, I can’t figure out why I’m getting errors here. I’ve used transactions, and prepared statements, and last_insert_id() before, but when I combine them all, I get errors. If you can take a look at this:

<?php
// Connection details above
// Create connection for prepared statements
$db = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

try {
    // First of all, begin a transaction
    mysqli_begin_transaction($conn);

    mysqli_autocommit($conn, FALSE);

    // A set of queries; if one fails, an exception should be thrown
    $result = mysqli_query($conn, " INSERT INTO vendors (v_status) VALUES (0) "); //automatically gives v_ref an auto_incremented value
    if (!$result) {
    	echo "1st query unsuccessful";
		mysqli_rollback($conn);
	}

	//this statements inserts the table 'vendor's primary key into v_i_ref here:
    $stmt = $db->prepare(" INSERT INTO vendor_information (v_i_ref, v_i_email, v_i_username, v_i_pass, v_i_province, v_i_town, v_i_agreement) VALUES (LAST_INSERT_ID(), ?, ?, ?, ?, ?, 1) ");
	
	$stmt->bind_param('sssss', $email, $username, $hashed_password, $province, $city);
	// $stmt->execute();							

	if ($stmt->execute()) {
		//statement executed successfully
	} else {
		echo "2nd query unsuccessful";
		echo $stmt->error;
		mysqli_rollback($conn);
	}

    $result = mysqli_query($conn, " INSERT INTO user_reputation (u_r_v_ref) SELECT MAX(v_ref) FROM vendors "); //this causes the transaction to exceed 50 seconds! thereby failing the transaction!
	if (!$result) {
		echo "3rd query unsuccessful";
		mysqli_rollback($conn);
	}

	$stmt = $db->prepare(" INSERT INTO ip_addresses (i_a_user_ref, i_a_address)  VALUES ((SELECT MAX(v_ref) FROM vendors), ?) ");
	$stmt->bind_param('s', $client_connection);

	if (!$stmt->execute()) {
		echo "4th query unsuccessful";
		mysqli_rollback($conn);
	}

	$stmt = $db->prepare(" INSERT INTO user_agents (u_a_v_ref, u_a_user_agent)  VALUES ( (SELECT MAX(v_ref) FROM vendors), ?) ");
	$stmt->bind_param('s', $client_useragent);
	
	if (!$stmt->execute()) {
		echo "5th query unsuccessful";
		mysqli_rollback($conn);
	}

	$result = mysqli_query($conn, " INSERT INTO pedlarcash_accounts (p_a_v_ref) SELECT MAX(v_ref) FROM vendors "); /*inserts vendor reference here*/
	if (!$result) {
		echo "6th query unsuccessful";
		mysqli_rollback($conn);
	}
	$result = mysqli_query($conn, " INSERT INTO pedlarcash_closing_balances(p_c_b_account) SELECT MAX(p_a_account_number) FROM pedlarcash_accounts "); /*inserts account number here*/
	if (!$result) {
		echo "7th query unsuccessful";
		mysqli_rollback($conn);
	}
    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed, and we can commit the transaction
    mysqli_commit($conn);

} catch (Exception $e) {
    // An exception has been thrown
    // We must rollback the transaction
    mysqli_rollback($conn);

    //Check if rows have been affected
	if (mysqli_affected_rows($conn) == 0) {
	echo "No rows have been affected!";
	} else {
	echo "Successfully completed the transaction!";
	}

}

If I use LAST_INSERT_ID() then a foreign key constraint fails, and if I use (SELECT MAX(v_ref) FROM vendors) then the code takes so long to execute that it fails the transaction with “Lock wait timeout exceeded; try restarting transaction”.

How is a transaction like this done?

Basically I have about 7 tables so that each time a user registers, his information is inserted into the various tables by using a transaction (it should never, ever, insert into half the tables only - therefore I must use a transaction).

And the way to link the user on every table is by using their ID. The id is a primary key in ‘vendors’ and a foreign key in every other table.

Can you see any apparent issue with the way the transaction is being executed? Any slight tip would be much appreciated…

[phdr]

First of all, you have two different database connections in the posted code. This results in two different database sessions, so, the last insert id and transaction won’t work between the two connections. Use a single connection. This is probably the reason for the unusual execution and error you are getting.

Next, getting the max() value won’t work correctly when there are concurrent visitors causing inserts.

Starting a transaction and setting autocommit to false accomplish the same thing. Use one or the other.

Lastly, if you have enabled exceptions for errors, most of the conditional logic in your code won’t be executed if there is an exception, and if you haven’t enabled exceptions for errors, the conditional logic isn’t preventing the remaining queries from executing, so you have a mix of executed and non-executed queries.

What your code should do is -

1. Enable exceptions for errors and then remove all the conditional logic that’s testing the result from each query.
2. Use one connection (you should also stick to either procedural or OOP notion).
3. After the 1st query (and the account query later), get the last insert id into an appropriately named php variable, that you will then use in the rest of the queries that need the value.
4. You should form the sql query statement in a php variable. This separates the sql syntax from the php code. This helps reduce mistakes and also lets you see that there is common php code being repeated that you can then convert to use a function/class method for to further simplify your code. This will also more easily let you convert your code to use the PDO extension since the sql query syntax will remain the same, only the php statements will change (see the general purpose PDO prepared/non-prepared query method in one of your previous threads.)

[astonecipher]

Try

	//this statements inserts the table 'vendor's primary key into v_i_ref here:
    $stmt = $db->prepare(" INSERT INTO vendor_information (v_i_ref, v_i_email, v_i_username, v_i_pass, v_i_province, v_i_town, v_i_agreement) VALUES (?, ?, ?, ?, ?, ?, 1) ");
	
	$stmt->bind_param('isssss', $db->insert_id, $email, $username, $hashed_password, $province, $city);
	// $stmt->execute();							

	if ($stmt->execute()) {

And you don’t need to do any rollbacks if the first query fails, it is the first query after all!

[mslilafowler]

you have two different database connections in the posted code

Touche! Could that be it?

[mslilafowler]

That looks neat! Will try it out.

–(Edit)

You guys both gave me the solution to this, I changed all the queries to handle one connection only as per @phdr’s advice, especially that part about placing the last inserted id into a variable which should be used for the rest of the queries (thumbs up, that was clever!).

And the best part was how you used the last inserted id as a binding parameter…Neat, neat! Wow, you wouldn’t imagine how long I was trying to get this to work.

It works perfectly, I nearly got a stroke when it worked