The migration to utf-8 delete text, data loss! (Page 2 of 2)

I've past this bug by doing a backup of tables, topics, posts and forums and import them back with some modification after the upgrade --> result http://futurama-france.fr/forum/index.php

As far as I understand the update script works just like you have described:
http://punbb.informer.com/trac/browser/ … e.php#L338

Before 1.3 release we had tested the update process and had added SET NAMES:
http://punbb.informer.com/trac/changese … update.php

Maybe this SET NAMES call is wrong, we'll continue testing.

No wonder the users are losing their data!

What this function does is, it assumes that there is UTF8 data in the table and modifies the structure to match that. If the assumption is wrong - and it will often be wrong - it will simply take data in current encoding and tell MySQL that it is in fact UTF8. Which leads to data loss in itself, and if it just happens that the source data contain sequences not permitted under UTF8, the string will likely get truncated at that point. When I was upgrading to 1.3, for example, my data was in cp1250...

And if old charset is NOT ISO-8859-1 and neither iconv and mb_convert_encoding exist, leaves the string unchanged but tells MySQL that it is UTF8.

What the upgrade should do, and what WOULD be foolproof provided that the user tells the correct encoding, would be a sequence of ALTER TABLEs:

1) ALTER TABLE ... ALTER [string_field] BLOB
2) ALTER TABLE ... ALTER [string_field] [original_type] [user's_encoding]
3) ALTER TABLE ... ALTER [string_field] [original_type] CHARACTER SET utf8

Or even just steps 1 and 2, those would suffice and might be even safer. Conversion to UTF8 can be done on-request thanks to SET NAMES utf8.

I fail to see how that is relevant - database conversion code will almost certainly need to be hard-coded for every database separately. That is, if you want a reliable code.

Well, my main database is Firebird so I can't really tell you details about PostgreSQL and SQLite.

With MySQL, you don't care what encoding the data is stored in the database. All you need to do to get UTF8 output, regardless of encoding actually used by the database, is:

1) Make sure table structure matches table data. Which is NOT the case with many PunBB 1.2 installations, including mine - PunBB 1.2 did not create the tables correctly.

2) Make sure SET NAMES utf8 is called before any other SQL command.

Even if #1 is not satisfied, this approach will not lead to data loss on old data - old posts will simply display incorrectly, but as soon as table structure is fixed to match the data, everything will be fine.

Upgrade script uses a much more dangerous approach of reading all data, converting it to UTF8 and writing it back.