We have well over 50 million rows in our obs table. Using the simple “update obs set uuid = uuid() where uuid is null” would bomb out with a “The total number of locks exceeds the lock table size” error.

I ran this for a few hours to get through all rows. (Note that it overwrites any uuids that are currently there)

Copy this into a sql script and run it with “source” in mysql at command line.

drop procedure update_uuids;
delimiter //
create procedure update_uuids() begin
set @max = (select max(obs_id) from obs);
set @x = 1;
repeat
set @y = @x + 100000;
update obs set uuid = uuid() where obs_id >=@x and obs_id <@y; set @x = @y; until @x > @max
end repeat;
end
//
delimiter ;
call update_uuids();

This is in the openmrs category tagged as , , ,

2 comments »

2 Responses

  1. Steve Says:

    Sorry if this is obvious, but what are the uuids actually used for in OpenMRS, since there are always other unique keys and foreign keys on all the pertinent tables? Pointing me to the right place in the documentation for this is of course preferable. Thanks!

  2. Ben Says:

    Uuids are document on the openmrs wiki here: http://openmrs.org/wiki/UUIDs . The short answer to your question is for synchronization across sites within one installation group

Leave a Comment




Your comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.