Migrating the VITASA database from MySQL to Sqlite3

Posted by wanchan on December 13th, 2017 filed in Programming

So, Dreamhost’s database connectivity from my VPS is proving pretty flaky. I hear that people run real workloads on their servers, but mine gets auth errors for 30-60 minutes a day. Not every day, but twice in the last three days for me. Previously, Dreamhost support has indicated that it was a transient problem with the server config, and even migrated me to a different MySQL database server. That fixed it for a little while, but the issue is occurring again, and support doesn’t seem interested in actually diagnosing and fixing it.

As an interim solution, I’ve decided to migrate my Rails application to AWS Lightsail. The catch is that Lightsail does not come with a free database like my Dreamhost account does, and I’m trying to keep costs down. Thus, this new copy of the production stack will backend with Sqlite3. I’m still using Passenger, this time with Nginx. During the migration, I ran into a few issues.

Migrating the data

This is how I wanted to roll – run mysqldump, maybe clean up the file a bit, then import it into sqlite3 on the commandline. Unfortunately, there seemed to be a number of commands that were incompatible, and frankly I’m not interested in becoming an expert on sqlite3 just for this.

So, my next attempt was to use Rails’ built-in tasks to dump the database in ruby form, and thus use ActiveRecord to reload the database on the new host using “rails db:setup”. That almost worked, except for two problems.

Time of Day Objects

For my Shift model, the start and end time are abstracted through the excellent ToD gem. The catch? When I run “rails db:dump”, it outputs the results in object notation, which can’t be reserialized with “rails db:seed”. The quickest solution was to write a ruby script to edit the resulting seed.rb file. This script would take in each line on $stdin, edit each #<Tod::TimeOfDay> object into a nice “HH:mm:ss” string, and write the results back to $stdout.

def update_line_tods(line)
 pattern = /#<Tod::TimeOfDay:0x\h+ @hour=(\d+), @minute=(\d+), @second=(\d+), @second_of_day=\d+>/
 while( match = line.match(pattern) )
   line.gsub!(pattern, "\"#{match[1].rjust(2,'0')}:#{match[2].rjust(2,'0')}:#{match[3].rjust(2,'0')}\"")
 end
 return line
end

while(s=gets)
  puts update_line_tods(s)
end

Great! That works very well. Now the next problem…

Bypassing User validation

I use the lovely “has_secure_password” method for my password-related fields. It works great… except that the seeds.rb file I generated by dumping the production database doesn’t have the original values for everyone’s password (obviously). And if you call User.create([{email: …, password_digest: …}]) like db:dump generates, you will get validation errors about missing values in password and password_confirmation. As best I can tell, the #create method does not have any way to bypass validation, but #save does. So, an addition to my script is necessary. This time, I need to take every record in the User.create( ) input array, and turn it into a pair of statements: u = User.new() and u.save(validate: false).  Thus:

def update_line_tods(line)
 pattern = /#<Tod::TimeOfDay:0x\h+ @hour=(\d+), @minute=(\d+), @second=(\d+), @second_of_day=\d+>/
 while( match = line.match(pattern) )
   line.gsub!(pattern, "\"#{match[1].rjust(2,'0')}:#{match[2].rjust(2,'0')}:#{match[3].rjust(2,'0')}\"")
 end
 return line
end


while(s=gets)
  s = update_line_tods(s)

  current_model = nil
  if match = s.match(/\A(.+)\.create/)
    current_model = match[1]
  end
  if current_model = 'User' && s.start_with?(" {email")
    puts "u = User.new(#{s.strip.gsub('},', '}')})"
    puts "u.save(:validate => false)"
  else
    puts s
  end
end

And we’re done! … almost. This still leaves the start and end of the old enormous User.create statement in place, which I felt it was easier to just delete manually. I’ll probably want to script it later, since I’ll need to run this procedure again when I migrate the service officially.

Summary

Unfortunately, the web services provided by this Rails application are consumed by mobile apps which are already published, and the app developer has hard-coded the hostname for the app. I am going to ask him to migrate to loading the hostname and base-path from our config.json file, which lives in S3. It’s already used to tell the app where to look for other things, so this would just be an incremental update to that logic. Once done, it will make it much easier to migrate the APIs to different hosts as we find a long-term home for this service.

In summary, thanks for the chore, Dreamhost. I’ll be cancelling my VPS services just as soon as I can migrate my users.

Leave a Comment

*