MySQL Forums
Forum List  »  Router & Proxy

LAST_INSERT_ID using triggers the safe way
Posted by: Kosta Psimoulis
Date: December 21, 2012 01:39PM

Hello,

Since triggers do not remember the last_insert_id updates, I created a trigger, a function and a lua script to handle that. I am gonna be doing rw splitting using the proxy-read-only-backend-addresses option. I know that there has been some modifications in the code to handle LAST_INSERT_ID calls to go through the same backend.
My question basically is, would the following script affect the modifications that have been done in mysql-proxy to handle this and how would the rw splitting work if I use the proxy-read-only-backend-addresses option ? Would it automatically read the script from /usr/share/mysql-proxy/rw-splitting.lua ? And between that script and my proxy-lua-script which one would have priority ?

trigger code
CREATE TRIGGER `t1_insert_after_trigger` AFTER INSERT ON `t1` FOR EACH ROW BEGIN
    INSERT INTO t2(name,address) VALUES(NEW.username,NEW.email);
    set @trigger_last_insert_id := (select last_insert_id());
END;

function code
CREATE FUNCTION `TRIGGER_LAST_INSERT_ID`() RETURNS int(11)
BEGIN
  DECLARE t_id INT;
  IF @trigger_last_insert_id IS NULL THEN
    SET t_id = 0;
  ELSE
    SET t_id = @trigger_last_insert_id;
  END IF;
  RETURN (t_id);
END

lua code
function read_query( packet )
   if string.byte(packet) == proxy.COM_QUERY then
     local query = string.sub(packet, 2)
     local replacing = false
     if string.match(string.upper(query), 'LAST_INSERT_ID') then
         query = string.gsub(query,'LAST_INSERT_ID', 'TRIGGER_LAST_INSERT_ID')
         replacing = true
     end
     if (replacing) then
         proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
         return proxy.PROXY_SEND_QUERY
     end
   end
end

Options: ReplyQuote


Subject
Views
Written By
Posted
LAST_INSERT_ID using triggers the safe way
4645
December 21, 2012 01:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.