Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an installation of trac, which uses MySQL. It has been upgraded so many times, moved servers etc. and chances are that the MySQL character sets were not always set correctly or consistently over the years. Currently all of them are utf8.

When attempting to upgrade the data using "trac-admin wiki update", I'm getting an error message that a byte ("UnicodeDecodeError: 'utf8' codec can't decode byte 0xa0 in position 4274: invalid start byte") is not valid unicode. Unfortunately trac-admin gives me no hint where (table/row/column) to look for that byte sequence, or what I could do to fix it.

My question is not about trac/trac-admin, however, it's about the database. How would you go about finding, "somewhere" in the database, the offending bytes, and replacing them with something that is at least valid utf8. I have attempted to mysqldump the database and to re-import it, but MySQL gives no indication that anything might be wrong. The invalid bytes get re-imported.

Ideas?

share|improve this question
add comment

2 Answers

You could try turning on the general query log, which logs every query the server receives, and then use that information to see what's being queried, and then issue those queries yourself and examine the data.

To see the current setting for the general log (location and whether it's enabled):

mysql> SHOW VARIABLES LIKE 'general_log%';  # shows the on/off status and file path

To turn it on:

mysql> SET GLOBAL general_log = on; # or off to disable

Remember to turn if off again when you're done, to avoid filling the disk and potentially degrading performance with excessive I/O.

There are also options for logging to a table instead of a file.

mysql> SHOW VARIABLES LIKE 'log_output';    # shows whether logging to FILE or TABLE
share|improve this answer
add comment

How much data do you have? You could compare length and char_length of data values to return all the multibyte data, but if you have a ton of data, this will be too much to visually process.

Start with something like: select [Column] from [Database].[Table] where length([Column]) != char_length([Column])

And use some OR statements in the where clause.

Narrowing down the problem through logs is probably where you need to start, though.

Can you provide any more info about the schema?

share|improve this answer
    
This is open-source trac, here's there schema: trac.edgewall.org/wiki/TracDev/DatabaseSchema –  Johannes Ernst Apr 7 '13 at 4:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.