Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

SELECT LAST_INSERT_ID does not work

by Vagabundo (Initiate)
on Jun 28, 2017 at 04:49 UTC ( #1193750=perlquestion: print w/replies, xml ) Need Help??

Vagabundo has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I have two scripts using the same mysql-database with DBI and the same table. One of this scripts add sometimes a lot of inserts.
The other script than has a problem to get the correct LAST_INSERT_ID.
It looks like the busy script makes entries at the same time between my Insert and my SELECT LAST_INSERT_ID.
Is that real possible and how can I prevent it.

My Code is:
my $sthopen = $dbh->prepare("INSERT INTO..... $sthopen->execute; &ErrorDBI; my $resid = $dbh->prepare("SELECT LAST_INSERT_ID()"); $resid->execute; &ErrorDBI; my @idrow = $resid->fetchrow_array;
for example:

The auto_increment entry is 2201 and the last_insert is 2203
or 2504 and 2505
and so on.....
Has anybody an idea for an solution?

Replies are listed 'Best First'.
Re: SELECT LAST_INSERT_ID does not work
by Corion (Pope) on Jun 28, 2017 at 06:05 UTC
Re: SELECT LAST_INSERT_ID does not work
by poj (Abbot) on Jun 28, 2017 at 06:33 UTC

    From the docs

    Important
    If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.
    poj
Re: SELECT LAST_INSERT_ID does not work
by Marshall (Canon) on Jun 28, 2017 at 06:06 UTC
    I am unsure what the problem is. MySql doc says:

    For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.
    Out of curiosity, why do you even care what the last auto_increment unique number that your client used was? Why does it matter to you? Other clients could be many, many numbers ahead of you. You can know this number for your last insert, then I guess you use that as a field for an entry into a different table? But why would you want to do that?
Re: SELECT LAST_INSERT_ID does not work
by afoken (Canon) on Jun 28, 2017 at 18:01 UTC
    my $sthopen = $dbh->prepare("INSERT INTO..... $sthopen->execute; &ErrorDBI; my $resid = $dbh->prepare("SELECT LAST_INSERT_ID()"); $resid->execute; &ErrorDBI; my @idrow = $resid->fetchrow_array;
    • The portable way to get the last inserted ID is to use DBI's last_insert_id() method. Well, it should be. The problem of getting automatically generated IDs is solved in very different ways in different databases, and this method should hide the implementation details. Unfortunately, some databases require extra arguments for the method call, some databases don't always return a value depending on how the insert statement was prepared. Anyway, better use that method instead of a DB-specific query. In the worst case, both are equal, in the best case, DBI can use a better API function of the database to get the value.
    • You don't need the ampersand in Perl 5 to call a function. Quite the opposite is true: You should avoid the ampersand in Perl 5 when calling functions, as it can introduce nasty, hard-to-debug errors. The ampersand was needed in Perl 4, but that was decades ago. Read more at Re^2: Merge log files causing Out of Memory (just a note on ampersand).
    • DBI can automatically handle all errors for you, you don't have to explicitly check for errors. Add the RaiseError attribute with a true value to the connect() call and any failed command will raise an error (i.e. call die).

    Alexander

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
Re: SELECT LAST_INSERT_ID does not work
by chacham (Prior) on Jun 28, 2017 at 20:37 UTC

    As others have pointed out, this is a per-connection function, so if there are multiple clients, that may be affecting it. Also, triggers or the like that grab a new id (even if left unused) may b affecting it.

    One kludge, perhaps, to guarantee it to work, would be to use a stored procedure to do the insert, run the select, and return the result from the select to you.

      Also, triggers or the like that grab a new id (even if left unused) may b affecting it. One kludge, perhaps, to guarantee it to work, would be to use a stored procedure to do the insert, run the select, and return the result from the select to you.

      If a trigger modifies the data for LAST_INSERT_ID, how could a stored procedure executing INSERT and SELECT LAST_INSERT_ID() guarantee that the data is not overwritten?

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

        It cannot guarantee it was not overwritten, but it can guarantee what was inserted by that statement.

        If a trigger runs as a result of the select, the insert will not finish until the trigger is finished, so the next statement should be accurate. (I am assuming noone is trying to specifically avoid detection with some fancy logic in the trigger.)

Re: SELECT LAST_INSERT_ID does not work
by Anonymous Monk on Jun 28, 2017 at 05:20 UTC

    The shown code could not be as such. It must have been distributed among different programs, else your query does not make sense. At least be self-consistent.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1193750]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (6)
As of 2020-11-25 15:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?