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()
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:
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: Calling Stored Procedures from PHP
Filipe Freitas wrote:
Hi,
This is not a mysql thing but maybe you can help me.
I want to call a stored procedure from PHP, so I tried it like normal
querys: mysql_query("CALL mySP();"); with no success.
What is happening when you make the call? That might help get some more
precise advice.
My understanding of using PHP with MySQL stored procedures is that you
have to call them through the mysqli extension. It doesn't appear that
you are using them. With them your would be using the mysqli_query()
function.
I don't do a lot of PHP, but I stumbled into a similar problem and using
the mysqli extensions solved the issue.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create function in mysql 5.0.26-3
Anakreon Mejdi wrote:
I am trying to create a stored function.
From the docs I saw the code for this function:
CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');
The version from the docs did not contain the DETERMINISTIC keyword.
I added because it would not compile.
The problem is that if I change the code to:
CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
DETERMINISTIC
LANGUAGE SQL
BEGIN
RETURN CONCAT('Hello, ',s,'!');
END;
I get the error message:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'END'
at line 1
In this example you need to change the delimiter to something other than
; otherwise the MySQL client things that's the stopping point for a
statement and attempts to process.
mysql> delimiter //
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
-> DETERMINISTIC
-> LANGUAGE SQL
-> BEGIN
-> RETURN CONCAT('Hello, ',s,'!');
-> END;
->
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> select hello("Mike");
+---+
| hello("Mike") |
+---+
| Hello, Mike! |
+---+
1 row in set (0.00 sec)
Hope that helps. Functions are covered pretty well in Pro MySQL
(Apress). I'm one of the authors.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlhotcopy errors osx
DBD::mysqlPP is the pure Perl driver for DBI. Is there a reason you are
using that instead of DBD::mysql? I haven't done Perl/MySQL on OS X
before but on other platforms (and from the error you're getting here)
DBD::mysql is the typical driver for using DBI with MySQL. It also is
the driver that mysqlhotcopy is built to use.
Mike
James Tu wrote:
The first time I tried to run the mysqlhotcopy script, I got an error
that indicated that Perl couldn't find the DBI module.
So, I found a site (http://www.quicomm.com/apm_dbddbi.htm) that
stepped me through making and installing
MySQL DBD and the DBI modules(? don't know if I'm using the right
terminology here)
However, during the 'make' step of the DBD module I got a warning:
prompt$ sudo Perl Makefile.PL
Checking if your kit is complete...
Looks good
Warning: prerequisite Net::MySQL 0.08 not found.
Writing Makefile for DBD::mysqlPP
So I decided to try to 'make install' anyway and I got...
$ sudo make install
Installing /Library/Perl/5.8.6/DBD/mysqlPP.pm
Installing /usr/local/man/man3/DBD::mysqlPP.3pm
Writing
/Library/Perl/5.8.6/darwin-thread-multi-2level/auto/DBD/mysqlPP/.packlist
Appending installation info to
//System/Library/Perl/5.8.6/darwin-thread-multi-2level/perllocal.pod
trying to run mysqlhotcopy again, I got...
$ mysqlhotcopy --dryrun imaginon
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC
contains: /System/Library/Perl/5.8.6/darwin-thread-multi-2level
/System/Library/Perl/5.8.6
/Library/Perl/5.8.6/darwin-thread-multi-2level /Library/Perl/5.8.6
/Library/Perl /Network/Library/Perl/5.8.6/darwin-thread-multi-2level
/Network/Library/Perl/5.8.6 /Network/Library/Perl
/System/Library/Perl/Extras/5.8.6/darwin-thread-multi-2level
/System/Library/Perl/Extras/5.8.6 /Library/Perl/5.8.1 .) at (eval 7)
line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Proxy, Sponge, mysqlPP.
at /usr/local/mysql/bin/mysqlhotcopy line 178
line 178 of the script looks like:
my $dbh =
DBI->connect("dbi:mysql:$dsn;mysql_read_default_group=mysqlhotcopy",
$opt{user}, $opt{password},
should I change the "dbi:mysql" part to "dbi:mysqlPP" ???
or should I fix the earlier warning about Net:MySQL 0.08 not found
first. Do I just install the Net package?
...OK
I actually downloaded and installed Net:MySQL...
sudo Perl Makefile.PL
sudo make
sudo make test
sudo make install
...all went ok
then I did the same steps for DBD
...all went ok and no warnings...
But I'm still getting the same 'line 178' error.
What am I not doing correctly?
Thanks.
-James
--MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How Do I Know If mySQL is using MyISAM or InnoDB?
This is in 4.0, it has changed in more recent versions. Mike Kruckenberg wrote: For any specific table if you do: show create table ; It will tell you what the able was created using. To create tables using a specific engine add ENGINE= to the end of your create statement. To see the default that is used (I think this is what the table_type variable does): mysql> show variables like 'table_type'; +---++ | Variable_name | Value | +---++ | table_type| MYISAM | +---++ 1 row in set (0.00 sec) John Kopanas wrote: Is there a command at the command line that can tell me if I am using MyISAM or InnoDB? Thanks :-). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How Do I Know If mySQL is using MyISAM or InnoDB?
For any specific table if you do: show create table ; It will tell you what the able was created using. To create tables using a specific engine add ENGINE= to the end of your create statement. To see the default that is used (I think this is what the table_type variable does): mysql> show variables like 'table_type'; +---++ | Variable_name | Value | +---++ | table_type| MYISAM | +---++ 1 row in set (0.00 sec) John Kopanas wrote: Is there a command at the command line that can tell me if I am using MyISAM or InnoDB? Thanks :-). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
