Re: InnoDB Transaction and LAST_INSERT_ID()
Mike Kruckenberg wrote: mysql SET @staff_id = LAST_INSERT_ID(); Query OK, 0 rows affected (0.01 sec) I don't know if this behaviour has changed in later versions of mysql, but using session variables, although lovely, was the quickest way to break replication (at least up to and including 4.0.27) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Transaction and LAST_INSERT_ID()
Hi List,
Let's suppose I have these two tables:
CREATE TABLE `changes` (
`ID` int(12) unsigned NOT NULL auto_increment,
`Key` varchar(25) collate latin1_general_cs NOT NULL default '',
`Table` varchar(25) collate latin1_general_cs NOT NULL default '',
`Value` text collate latin1_general_cs NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
AUTO_INCREMENT=1;
CREATE TABLE `staff` (
`ID` int(3) unsigned NOT NULL auto_increment,
`Name` varchar(35) collate latin1_general_cs NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
AUTO_INCREMENT=1;
The idea is to have a audit trail to record the changes made. So, I want to
insert a new record in the staff table and right after this, insert a
record in the changes table.
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Value`) VALUES (LAST_INSERT_ID(),
'staff', 'ABC');
COMMIT;
SET AUTOCOMMIT=1;
This works fine in my test environment, however what about many users doing
at the same time. Does the LAST_INSERT_ID() get the correct ID for each
user?
Is there a better way to do this or this is fine? I will be using this with
PHP4.
Thanks for any help.
Andre
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Transaction and LAST_INSERT_ID()
Andre Matos wrote:
The idea is to have a audit trail to record the changes made. So, I want to
insert a new record in the staff table and right after this, insert a
record in the changes table.
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Value`) VALUES (LAST_INSERT_ID(),
'staff', 'ABC');
COMMIT;
SET AUTOCOMMIT=1;
This works fine in my test environment, however what about many users doing
at the same time. Does the LAST_INSERT_ID() get the correct ID for each
user?
LAST_INSERT_ID() is connection-specific so the ID will be the one that
was assigned during this particular PHP page's connection to the database.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Transaction and LAST_INSERT_ID()
Andre Matos wrote:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Value`) VALUES (LAST_INSERT_ID(),
'staff', 'ABC');
COMMIT;
SET AUTOCOMMIT=1;
This works fine in my test environment, however what about many users doing
at the same time. Does the LAST_INSERT_ID() get the correct ID for each
user?
Is there a better way to do this or this is fine? I will be using this with
PHP4.
To further clarify (my initial reply didn't give much detail), when an
auto increment value is created for inserting it is in the scope of the
current connection, and is not changed by the outcome of the transaction.
If you follow the string of SQL statements against your tables you'll
see that the ID assigned to the record is not released on a rollback,
the second insert gets a new auto increment value.
mysql SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO staff (`Name`) VALUES ('ABC');
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(),
- 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select * from staff;
++--+
| ID | Name |
++--+
| 1 | ABC |
++--+
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+
| ID | Key | Table | Value |
++-+---+---+
| 1 | 1 | staff | ABC |
++-+---+---+
1 row in set (0.00 sec)
mysql select last_insert_id();
+--+
| last_insert_id() |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql rollback;
Query OK, 0 rows affected (0.26 sec)
mysql select * from staff;
Empty set (0.00 sec)
mysql select * from changes;
Empty set (0.00 sec)
mysql INSERT INTO staff (`Name`) VALUES ('ABC');
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(),
- 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select * from staff;
++--+
| ID | Name |
++--+
| 2 | ABC |
++--+
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+
| ID | Key | Table | Value |
++-+---+---+
| 2 | 2 | staff | ABC |
++-+---+---+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Transaction and LAST_INSERT_ID()
Thanks Mike.
I understand the possible gaps that I might have if I use the ROLLBACK.
This is acceptable in my case.
What I really want to avoid is what I am doing now: open one transaction to
insert, or update, or delete certain information and close with the commit.
Then, I get the LAST_INSERT_ID() and open another transaction to write my
audit trail. However, if the first one went through ok but if I got a
problem at the second transaction, I need to delete the inserted or updated
or move back the deleted information. This doesn't work well.
Let's expand my staff and change tables to have this structure to
simulate my problem:
++--++
| ID | Name | Gender |
++--++
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
And do this:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `M`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'ID', LAST_INSERT_ID());
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
I will get something like this:
mysql select * from staff;
++--++
| ID | Name | Gender |
++--++
| 1 | ABC | M |
++--++
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
| 1 | 1 | staff | ID| 1 |
++-+---+---+---+
| 2 | 1 | staff | Name | ABC |
++-+---+---+---+
| 3 | 2 | staff | Gender| M |
++-+---+---+---+
3 row in set (0.00 sec)
See that I have a problem in the third line at the Key column where I
should have 1 but I got 2 instead. This happened because of
LAST_INSERT_ID() used the ID from the changes table instead of the desired
staff table.
Is there any way to avoid this? What about the mysql_insert_id()?
Andre
On 11/28/06 7:50 PM, Mike Kruckenberg [EMAIL PROTECTED] wrote:
Andre Matos wrote:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Value`) VALUES (LAST_INSERT_ID(),
'staff', 'ABC');
COMMIT;
SET AUTOCOMMIT=1;
This works fine in my test environment, however what about many users doing
at the same time. Does the LAST_INSERT_ID() get the correct ID for each
user?
Is there a better way to do this or this is fine? I will be using this with
PHP4.
To further clarify (my initial reply didn't give much detail), when an
auto increment value is created for inserting it is in the scope of the
current connection, and is not changed by the outcome of the transaction.
If you follow the string of SQL statements against your tables you'll
see that the ID assigned to the record is not released on a rollback,
the second insert gets a new auto increment value.
mysql SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO staff (`Name`) VALUES ('ABC');
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(),
- 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select * from staff;
++--+
| ID | Name |
++--+
| 1 | ABC |
++--+
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+
| ID | Key | Table | Value |
++-+---+---+
| 1 | 1 | staff | ABC |
++-+---+---+
1 row in set (0.00 sec)
mysql select last_insert_id();
+--+
| last_insert_id() |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql rollback;
Query OK, 0 rows affected (0.26 sec)
mysql select * from staff;
Empty set (0.00 sec)
mysql select * from changes;
Empty set (0.00 sec)
mysql INSERT INTO staff (`Name`) VALUES ('ABC');
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(),
- 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select * from staff;
++--+
| ID | Name |
++--+
| 2 | ABC |
++--+
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+
| ID | Key | Table | Value |
++-+---+---+
| 2 | 2 | staff | ABC |
++-+---+---+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Transaction and LAST_INSERT_ID()
Andre Matos wrote:
Thanks Mike.
I understand the possible gaps that I might have if I use the ROLLBACK.
This is acceptable in my case.
What I really want to avoid is what I am doing now: open one transaction to
insert, or update, or delete certain information and close with the commit.
Then, I get the LAST_INSERT_ID() and open another transaction to write my
audit trail. However, if the first one went through ok but if I got a
problem at the second transaction, I need to delete the inserted or updated
or move back the deleted information. This doesn't work well.
Let's expand my staff and change tables to have this structure to
simulate my problem:
++--++
| ID | Name | Gender |
++--++
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
And do this:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `M`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'ID', LAST_INSERT_ID());
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
I will get something like this:
mysql select * from staff;
++--++
| ID | Name | Gender |
++--++
| 1 | ABC | M |
++--++
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
| 1 | 1 | staff | ID| 1 |
++-+---+---+---+
| 2 | 1 | staff | Name | ABC |
++-+---+---+---+
| 3 | 2 | staff | Gender| M |
++-+---+---+---+
3 row in set (0.00 sec)
See that I have a problem in the third line at the Key column where I
should have 1 but I got 2 instead. This happened because of
LAST_INSERT_ID() used the ID from the changes table instead of the desired
staff table.
Is there any way to avoid this? What about the mysql_insert_id()?
I see. In this case you could make it two operations and use the
mysql_insert_id() to capture the id from the first insert, setting a
variable to that in PHP and using that variable to ensure the same number.
What I would consider is setting a database variable inside the
transaction to store the id - the @ signifies it's a session variable
that is specific to this connection:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `Gender`) VALUES ('ABC','M');
SET @staff_id = LAST_INSERT_ID();
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'ID', @staff_id);
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
So you store the value after the first insert and then reuse. You can
see it in the following string of SQL commands to demonstrate:
mysql INSERT INTO staff (`Name`, `Gender`) VALUES ('ABC','M');
Query OK, 1 row affected (0.01 sec)
mysql SET @staff_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.01 sec)
mysql SELECT @staff_id;
+---+
| @staff_id |
+---+
| 3 |
+---+
1 row in set (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(), 'staff', 'ABC');
Query OK, 1 row affected (0.01 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(), 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select LAST_INSERT_ID();
+--+
| LAST_INSERT_ID() |
+--+
|5 |
+--+
1 row in set (0.00 sec)
mysql SELECT @staff_id;
+---+
| @staff_id |
+---+
| 3 |
+---+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Transaction and LAST_INSERT_ID()
Thanks for all your help Mike.
Problem solved. I divided to process in two parts: one write the
insert/update/delete and then write the changes in the audit trail. All this
inside one transaction. If the first part fails, ROLLBACK. If the second
part fails, ROLLBACK, otherwise, if both were done ok, then COMMIT.
I just wanted to have all this in only one part, but that's fine. It's
working fine.
Final question: Can I create an audit trail using TRIGGER in MySQL 5? This
would be the best because any changes in the database (insert/update/delete)
will start the trigger which will be responsible for writing the audit
trail.
Thanks again!!!
Andre
On 11/28/06 9:22 PM, Mike Kruckenberg [EMAIL PROTECTED] wrote:
Andre Matos wrote:
Thanks Mike.
I understand the possible gaps that I might have if I use the ROLLBACK.
This is acceptable in my case.
What I really want to avoid is what I am doing now: open one transaction to
insert, or update, or delete certain information and close with the commit.
Then, I get the LAST_INSERT_ID() and open another transaction to write my
audit trail. However, if the first one went through ok but if I got a
problem at the second transaction, I need to delete the inserted or updated
or move back the deleted information. This doesn't work well.
Let's expand my staff and change tables to have this structure to
simulate my problem:
++--++
| ID | Name | Gender |
++--++
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
And do this:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `M`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'ID', LAST_INSERT_ID());
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
I will get something like this:
mysql select * from staff;
++--++
| ID | Name | Gender |
++--++
| 1 | ABC | M |
++--++
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
| 1 | 1 | staff | ID| 1 |
++-+---+---+---+
| 2 | 1 | staff | Name | ABC |
++-+---+---+---+
| 3 | 2 | staff | Gender| M |
++-+---+---+---+
3 row in set (0.00 sec)
See that I have a problem in the third line at the Key column where I
should have 1 but I got 2 instead. This happened because of
LAST_INSERT_ID() used the ID from the changes table instead of the desired
staff table.
Is there any way to avoid this? What about the mysql_insert_id()?
I see. In this case you could make it two operations and use the
mysql_insert_id() to capture the id from the first insert, setting a
variable to that in PHP and using that variable to ensure the same number.
What I would consider is setting a database variable inside the
transaction to store the id - the @ signifies it's a session variable
that is specific to this connection:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `Gender`) VALUES ('ABC','M');
SET @staff_id = LAST_INSERT_ID();
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'ID', @staff_id);
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
So you store the value after the first insert and then reuse. You can
see it in the following string of SQL commands to demonstrate:
mysql INSERT INTO staff (`Name`, `Gender`) VALUES ('ABC','M');
Query OK, 1 row affected (0.01 sec)
mysql SET @staff_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.01 sec)
mysql SELECT @staff_id;
+---+
| @staff_id |
+---+
| 3 |
+---+
1 row in set (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(), 'staff', 'ABC');
Query OK, 1 row affected (0.01 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(), 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select LAST_INSERT_ID();
+--+
| LAST_INSERT_ID() |
+--+
|5 |
+--+
1 row in set (0.00 sec)
mysql SELECT @staff_id;
+---+
| @staff_id |
+---+
| 3 |
+---+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
