Skip to content

Instantly share code, notes, and snippets.

@scotchi
Last active April 15, 2020 21:54
Show Gist options
  • Save scotchi/0823048d923ab506db858dd5e90a5dc9 to your computer and use it in GitHub Desktop.
Save scotchi/0823048d923ab506db858dd5e90a5dc9 to your computer and use it in GitHub Desktop.
Convert Wordpress tables to UTF-8 (when they already have UTF-8 data stored as Latin1)
#!/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
@stas00
Copy link

stas00 commented Apr 15, 2020

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).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment