While troubleshooting some issues with an application that a customer was migrating to their Netscaler (and hence DataStream) platform. In general the application worked well, but when using the built-in MySQL Last_Insert_ID() function, rather than returning the value of the last inserted row, the value returned was always zero. It turns out that this is a consequence of the Last_Insert_ID being a function that uses “connection affinity”. In fact, to quote the MySQL page I have just linked:

“For LAST_INSERT_ID() , the most recently generated ID is maintained in the server on a per-connection basis”

Therefore, with the Netscaler acting as a proxy between the client and the MySQL server (and providing all those nifty DataStream benefits like connection multiplexing), connection affinity cannot be relied upon. As a result, if you call the Last_Insert_ID function separately following an insert, you get zero. Fortunately, there are other options.

While testing and confirming this behavior I was easily able to recreate the behavior when I called the function as part of a subsequent SELECT statement or similar. However, because I was using Perl and the MySQL DBI driver to do my testing, I also used a shortcut method in DBI to get the last insert ID. Here are two Perl snippets representing the different methods:

# print out the DBI stored Last Insert ID
my $result = $dbh->last_insert_id(undef, undef, tbl_insert_test, col1_auto);
print "DBI Last Insert ID is: $result\n";
# use the select LAST_INSERT_ID syntax to get the ID
$sth = $dbh->prepare('SELECT LAST_INSERT_ID() from tbl_insert_test');

The code is relatively unimportant, I have just included it for completeness. The interesting part was that the DBI method worked, and returned the ID correctly each time. The SELECT statement returned zero, just as the customer had originally reported. By looking at traces, the mystery was solved. It turns out that the Perl DBI programmers are (unsurprisingly) very clever and take advantage of the fact that MySQL returns the Last Insert ID as part of the “OK” message for a successful INSERT. As a result, no subsequent query/connection (and hence no connection affinity) is actually needed to return the appropriate ID. See the snippet below from a Wireshark trace showing the ID in an OK response:

Insert ID in MySQL OK Response
Insert ID in MySQL OK

Not only does this solve our little problem with DataStream, it also saves a round-trip to the database and is generally a more elegant and efficient solution to the problem of obtaining the ID from an INSERT.  The customer was happy enough to make the change as a result and a major stumbling block in the migration was removed.