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 »

OpenMRS recently added a non-null uuid column to every table.  All of our dbunit xml files needed to be updated to insert values for this attribute.  I wrote the following shell script to loop over all xml files under the current folder and add a uuid attribute to all rows that didn’t have an attribute already.  There is a bit of logic in there to ignore certain tables because those tables didn’t actually get any uuids.

…read the rest of this entry »

This is in the openmrs category tagged as , , ,

Add a comment »