While troubleshooting problems with an application that has been a customer to migrate to their Netscaler (and therefore DataStream) platform. In general, the application worked well, but when you use the built-in MySQL LAST_INSERT_ID (), rather than return the value of the last inserted line, the return value is always zero. It turns out that this is a consequence of LAST_INSERT_ID being a function that uses "connection affinity". In fact, to quote the MySQL page I just linked:
"In LAST_INSERT_ID (), the most recently generated ID is maintained in the server on a person connection basis "
therefore the Netscaler acting as a proxy between the client and MySQL server (and providing all the benefits of Datastream clever as multiplex connection), the connection affinity can not be relied on. Therefore, if you call LAST_INSERT_ID separately following an insert, you get zero. Fortunately, there are other options.
By testing and confirming this behavior, I am easily able to recreate the behavior when I called the function as part of a subsequent or similar SELECT statement. However, because I was using Perl and MySQL DBI driver for my tests, I also used a shortcut method in DBI to get the last insert ID. Here are two excerpts Perl representing different methods:
# print the stored ID DBI Last Insert my $ result = $ dbh-> LAST_INSERT_ID (undef, undef, tbl_insert_test, col1_auto); print "DBI Last Insert ID is: $ result n"; # Use the LAST_INSERT_ID select syntax for the ID $ sth = $ dbh-> prepare ( "SELECT LAST_INSERT_ID () from tbl_insert_test '); $ Sth-> run;
The code is relatively small, I just included for completeness. The interesting part is that the DBI method worked, and returned the ID correctly every time. The SELECT statement is returned to zero, as the client had initially reported. Looking at the evidence, the mystery was solved. It turns out that the DBI programmers are (unsurprisingly) very smart and take advantage of the fact that MySQL returns the last inserted ID in the "OK" message for a successful INSERT. Therefore, no request / subsequent connection (and therefore no connection affinity) is really necessary to return the appropriate ID. See the excerpt below from a trace showing Wireshark ID in an OK response:
this not only solves our little problem with DataStream, it also saves a round trip to the database and is generally a more elegant and effective solution to the problem of obtaining the ID an INSERT. The client was happy enough to change as a result and a major obstacle in the migration was deleted.
0 Komentar