Tag Archives: mysql

GitlabHQ – SQLite to mySQL

Posted on by
1

As our GitlabHQ setup at SRMvision is mean to stay, we wanted to migrate from the standard SQLite database to an easier to manage for us : mySQL.
The steps are quite straighforward, first, setup mySQL :

create database gitlab; 
grant all privileges on gitlab.* to "gitlab" identified by "gitlab"; 
flush privileges;

Then prepare to dump the data out of GitlabHQ, stop your webserver, then, from your GitlabHQ folder : 

bundle exec rake db:data:dump RAILS_ENV=production

this will create a db/data.yml file. All you have to do now is reconfigure GitlabHQ to connect to mySQL and reimport the data. The database configuration takes place in the config/database.yml file, backup your existing file and rename the database.yml.example :

mv config/database.yml config/database.yml.old
cp config/database.yml.example config/database.yml

Then edit the file to reflect your new configuration, only the production section is relevant in our case :

development:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: gitlabhq_development
  pool: 5
  username: root
  password: "secure password"
  # socket: /tmp/mysql.sock

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: gitlabhq_test
  pool: 5
  username: root
  password: "secure password"
  # socket: /tmp/mysql.sock

production:
  adapter: mysql2
  encoding: utf8
  reconnect: true # allows to autoreconnect if the mysql service restarts
  database: gitlab # to replace with your database name
  pool: 5
  username: gitlab # to replace with your credentials
  password: "gitlab" # to replace with your credentials
  socket: /var/run/mysqld/mysqld.sock # mysql socket location on debian

Once this is done, you have to create the schema and restore your data :

bundle exec rake db:setup RAILS_ENV=production
bundle exec rake db:data:load RAILS_ENV=production

Job’s done, you just need to restart the GitlabHQ server now (nginx, apache…). If you encounter a problem and this does not work as expected, you just have to restore the database.yml.old file to go back to SQLite.

Convert a mySQL Database from latin1 to UTF-8

Posted on by
Reply

A frequent problem when it comes to internationalisation is proper handling of different charset. When you’re using Java and Maven it is relatively easy to set up source encoding to UTF-8, but the frequent point of failure is in the SQL database.


If you use mySQL, and you have latin1 tables, but you should have UTF-8 instead, use this little script to convert from latin1 to UTF-8 : 

mysqldump --user=${USER} --password=${PASS} --default-character-set=latin1 --skip-set-charset ${DATABASE} > dump.sql;
sed -r ‘s/latin1/utf8/g’ dump.sql > dump_utf.sql
mysql --user=${USER} --password=${PASS} --execute=”DROP DATABASE ${DATABASE}; CREATE DATABASE ${DATABASE} CHARACTER SET utf8 COLLATE utf8_general_ci;”
mysql --user=${USER} --password=${PASS} --default-character-set=utf8 dbname < dump_utf.sql

Generally speaking, don't hesitate to always put the --default-character-set=utf8 on all the mySQL commands you execute.
Don't forget to add at the end of your jdbc connection url the following parameters : "useUnicode=true&characterEncoding=UTF-8" to ensure you connect using UTF-8.