Tag Archives: Databases

Fixing messed-up encoding in MySQL

Posted on by
Reply

Currently working on my e107 Importer plugin, I was confronted today with badly-encoded data coming from my databases.

e107 migrated to full UTF-8 years ago, but I must have messed the upgrade process at the time. That was my conclusion when I took a close look to my tables: all of them seems to be set to Latin-1 but contain UTF-8 data. Here are screenshots from SQLBuddy (a great light-weight MySQL manager) showing just that:

To fix this, I first tried to use the following command I found on the web:

mysql --database=e107db -B -N -e "SHOW TABLES"  | awk '{print "ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"}' | mysql --database=e107db

But this doesn’t work, as it not only change the encoding of the table, but also transcode the data inside the table.

Let’s try something else. First, we’ll export the database to a dump file, of which the encoding is forced to Latin-1:

mysqldump -a -c -e --no-create-db --add-drop-table --default-character-set=latin1 --databases 'e107db' > ./e107-data.sql

Now the trick is to change the CHARSET parameter of all CREATE TABLE directives to UTF-8:

sed -i 's/CHARSET=latin1/CHARSET=utf8/g' ./e107-data.sql

We’ll also change the NAMES directive to force MySQL to handle imported data as UTF-8:

sed -i 's/SET NAMES latin1/SET NAMES utf8/g' ./e107-data.sql

Then we’re free to import the result in a new UTF-8 database:

sed -i 's/USE `e107db`;/#USE `e107db`;/g' ./e107-data.sql
mysql --execute="CREATE DATABASE e107db_new CHARACTER SET=utf8"
mysql --database=e107db_new < ./e107-data.sql

And now, accentuated characters appears as they should in our database, meaning we’ve fixed all the mess ! :)


PS: I found another alternative method (look at the end of the linked page) which consists of temporarily handling TEXT fields as BLOB, to have MySQL treat them as binary content (thus skipping character transcoding). Haven’t tested this but sounds tricky.

How-to export/backup Lotus Notes mails

Posted on by
4

You are using Lotus Notes as your mail platform. Unfortunately your mailbox has a quota you’ve already reached and you need space. A solution consist in exporting regularly your mails on your local machine to free up your inbox. Here is a little article documenting the export procedure using the fat desktop client.

If screenshots were taken with a french version, instructions given here are for the english one. This will give you enough clues to perform the export whatever the localisation is. The Lotus Notes version I used was the 7.0.2 release.

So first, let’s start Notes and open your mailbox. You should be on a screen similar to this one:

Then, go to the File › Database › New Copy menu:

And you’ll get an export screen that’ll let you choose where to create a local copy of your database:

This will generate a .nsf file containing all your current mail.

Now that you have a backup, you are free to delete all your mails in Lotus Notes. By following this procedure regulary, you can create yearly or monthly archives of you mails without reaching the mailbox quota ! For example, this is how my local archive folder looks like:

MySQL commands

Posted on by
1
  • List all users in the current MySQL server:
    mysql> SELECT User, Host FROM mysql.user;
  • Remove a user from the server:
    mysql> DROP USER '<User>'@'<Host>';
  • Remove all binary logs:
    mysql> RESET MASTER;
  • Delete all binary logs but keep a week worth of logs:
    mysql --verbose --execute="PURGE BINARY LOGS BEFORE '`date +"%Y-%m-%d" -d last-week`';"

    And if you put this in a cron-tab, don’t forget to escape percents:

    mysql --verbose --execute="PURGE BINARY LOGS BEFORE '`date +\%Y-\%m-\%d -d last-week`';"
  • Check, auto-repair and optimize all databases:
    mysqlcheck --auto-repair --optimize --all-databases
  • Export a database:
    mysqldump -u my_user "my-database" > data.sql
  • Here is a cron-able command to restart a MySQL service if no process found active:
    [ `ps axu | grep -v "grep" | grep --count "mysql"` -le 0 ] && /etc/init.d/mysql restart
  • Monitor the queries being run (source):
    watch -n 1 mysqladmin --user=XXXXX --password=XXXXX processlist
  • Get the list of default configuration parameters the server will use regardless of the values set in config files (source):
    mysqld --no-defaults --verbose --help
  • Migrate all tables of all databases from MyISAM to InnoDB:
    mysql --skip-column-names --silent --raw --execute="SELECT CONCAT(table_schema , '.', table_name) FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE' AND engine='MyISAM';" | xargs -I '{}' mysql --verbose --execute="ALTER TABLE {} ENGINE=InnoDB;"