Convert Wordpress tables to UTF-8 (when they already have UTF-8 data stored as Latin1)

convert_wordpress_tables_to_utf8.rb

#!/usr/bin/env ruby

require 'mysql2'

DATABASE, PREFIX = ARGV

client = Mysql2::Client.new(username: 'root', database: DATABASE)
tables = client.query("select table_name from information_schema.tables " +
                      "where table_schema='#{DATABASE}' and table_name like '#{PREFIX}%'",
                      as: :array).map(&:first)
tables.each do |table|
  puts table
  columns = client.query("select column_name from information_schema.columns " +
                         "where table_schema='#{DATABASE}' and table_name = '#{table}' " +
                         "and character_set_name != 'utf8'",
                         as: :array).map(&:first);

  client.query("alter table #{table} convert to character set utf8")

  updates = columns.map do |c|
    "#{c} = convert(cast(convert(#{c} using latin1) as binary) using utf8)"
  end.join(', ')

  next if updates.empty?

  puts "\t" + columns.join(', ')

  client.query("update #{table} set #{updates}");
end

Thank you for the script.

I was getting:

Traceback (most recent call last):
        6: from ./convert.rb:11:in `<main>'
        5: from ./convert.rb:11:in `each'
        4: from ./convert.rb:18:in `block in <main>'
        3: from /usr/lib/ruby/vendor_ruby/mysql2/client.rb:119:in `query'
        2: from /usr/lib/ruby/vendor_ruby/mysql2/client.rb:119:in `handle_interrupt'
        1: from /usr/lib/ruby/vendor_ruby/mysql2/client.rb:120:in `block in query'
/usr/lib/ruby/vendor_ruby/mysql2/client.rb:120:in `_query': Invalid default value for 'first_failure' (Mysql2::Error)

After some googling, I had it fixed by adding:

client.query("SET sql_mode = '';")

on top of the script (after client was created).