topic Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8 in Alfresco Archive

URGENT(help needed) - Alfresco migration from latin1 to UTF8

zvukovic — Fri, 20 May 2011 09:19:00 GMT

Hi all,We are using alfresco 3.2 instance and for database MySQL 5.1 in latin1 character set.We will need to migrate this database from latin1 to utf-8 in order to provide support for filenames in Greek characters.These are the steps we have folowed for migrating dataDump database and convert data&g

Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8

loftux — Fri, 20 May 2011 09:30:26 GMT

Is it a typo or did you import back the original dump in the last step?

mysql alfresco_utf8 < alfresco_latin1.sql‍

Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8

mrogers — Fri, 20 May 2011 09:51:32 GMT

And please don't cross post. I've deleted them for you :!:

Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8

zvukovic — Fri, 20 May 2011 10:55:53 GMT

Hi mrogers,
It is my typo, we have done correct import

> mysql alfresco_utf8 < alfresco_utf8-2.sql‍

Do you have any clue how to find solution for re-indexing(FullIndexRecoveryComponent,AVMFullIndexRecoveryComponent)?

Thanks
Zeljko

Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8

loftux — Fri, 20 May 2011 11:14:19 GMT

The only thing I can think of is that the conversion/import isn't correct after all. That during the reindexing Alfresco fails to read the database.
Have your tried to create a new repository (db+alf_data) that uses utf-8 and the connection string you are using? If all works well, then do an export of the database structure (excluding data) for that db and your converted db and compare them. Maybe you will find a difference there. If not, then there is something in the actual data.
What if you do a select in your converted db, does the data look correct then?

Some googling, with the import

mysql -h host -u username -p password –default_character_set utf8 database < file.sql ‍

since your file is now utf-8 encoded.

Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8

zvukovic — Fri, 20 May 2011 11:33:10 GMT

And please don't cross post. I've deleted them for you :!:

Sorry about that is urgent question for us so that is the reason why I posted several times.

Thanks

Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8

zvukovic — Fri, 20 May 2011 11:42:10 GMT

The only thing I can think of is that the conversion/import isn't correct after all. That during the reindexing Alfresco fails to read the database.
Have your tried to create a new repository (db+alf_data) that uses utf-8 and the connection string you are using? If all works well, then do an export of the database structure (excluding data) for that db and your converted db and compare them. Maybe you will find a difference there. If not, then there is something in the actual data.
What if you do a select in your converted db, does the data look correct then?

Some googling, with the import
mysql -h host -u username -p password –default_character_set utf8 database < file.sql ‍
since your file is now utf-8 encoded.

Hi Loftux.
To explain whole scenario : we are having 2 ALfresco instances one with latin1 and second one with utf-8.
This one with utf-8 character set is our DEV environment and there is everything ok and verified regarding Greek character set.
On PROD we have latin1 and we need to migrate it to use utf-8 because of upcoming request to have Greek character set for filenames in Alfresco.

We have tested all this and it is ok on DEV environment where we are already using utf-8.
Steps we have done in order to migrate data
1) we created one more UTF-8 schema alfresco_utf8 in DEV environment
2) then we dumped data from PROD
3) converted it to utf-8 in steps I have desribed
4) restored it to DEV in this new UTF-8 schema

After that we have alfresco db.url to use this new schema and to do FULL re-indexing but we are stucked because of the error I have posted.

Do you have any clue how we can resolve the issue with re-indexing because alfresco doesn;t want to start with this error we get.
Also if we try to start it without re-indexing it wan;t to start because it says we need to re-index content

Please do you any idea how we can overcome this?

Can we delete complete alf_data folder and restart alfresco, will it regenerate alf_data with data from db?

Thanks
Zeljko

Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8

loftux — Fri, 20 May 2011 11:50:13 GMT

The only thing, as mentioned, is that the import

> mysql alfresco_utf8 < alfresco_utf8-2.sql‍

is importing using ISO8859-1 instead of UTF-8 (your data file is now in utf-8). Thats why I would suggest

> mysql –default_character_set utf8 alfresco_utf8 < alfresco_utf8-2.sql‍

Other than that, I have no idea.

Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8

zvukovic — Fri, 20 May 2011 11:59:56 GMT

The only thing, as mentioned, is that the import
> mysql alfresco_utf8 < alfresco_utf8-2.sql‍
is importing using ISO8859-1 instead of UTF-8 (your data file is now in utf-8). Thats why I would suggest
> mysql –default_character_set utf8 alfresco_utf8 < alfresco_utf8-2.sql‍
Other than that, I have no idea.

Hmm, this is the statement we used to create database

> mysql create database alfresco_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;‍

Ok will import data like you stated and try once again re-indexing.

Thanks
Zeljko

Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8

zvukovic — Mon, 23 May 2011 13:15:28 GMT

Hi Loftux,
I have tried like you suggested but that didn't resolved our issue

> mysql –default_character_set utf8 alfresco_utf8 < alfresco_utf8-2.sql‍

Do you have any other idea what can help to migrate successfully from latin1 to utf-8?

Thanks
Zeljko

Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8

mrogers — Mon, 23 May 2011 13:40:07 GMT

The error posted above is, I suspect, because you have translated a serialized value which is stored in a BLOB. Not being text, the character set conversion will have corrupted your data.

You need to take care that you only convert text colums to utf-8.

Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8

zvukovic — Mon, 23 May 2011 15:19:25 GMT

The error posted above is, I suspect, because you have translated a serialized value which is stored in a BLOB. Not being text, the character set conversion will have corrupted your data.

You need to take care that you only convert text colums to utf-8.

Hi mrogers,
Thanks, for this info.
I will try to apply migration procedure only on text fields and hope that will resolve our issue.
Will let back to you when do migration only on text fields.

Thanks
Zeljko

Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8

zvukovic — Fri, 27 May 2011 14:23:31 GMT

Hi all,
We done migration only on text fiedls and then re-index was successful but now we are sseing some other issue.
I assume that is coming from content we have in ALFRESCO_HOME/alf_data/ folder.
Now we are checking that part. If someone need scripts which need to be executed in order to convert data from latin1 to utf-8 here is what we used:

Alter statements to migrate tables:

SELECT CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' convert to CHARACTER SET utf8 COLLATE utf8_bin;' ) FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'alfresco_utf8' AND TABLE_COLLATION = 'latin1_swedish_ci' ORDER BY TABLE_ROWS desc;
‍‍‍

Alter statements to migrate text columns:

                         SELECT CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;') FROM information_schema.COLUMNS 
                         WHERE TABLE_SCHEMA = 'alfresco_utf8' AND COLLATION_NAME = 'latin1_swedish_ci'and IS_NULLABLE='NO'
                         UNION
                         SELECT CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;') FROM information_schema.COLUMNS 
                         WHERE TABLE_SCHEMA = 'alfresco_utf8' AND COLLATION_NAME = 'latin1_swedish_ci'and IS_NULLABLE='YES';
‍‍‍‍‍‍

Thanks
Zeljko

Re: URGENT(help needed) - Alfresco migration from latin1 to UTF8

stegbth — Fri, 24 Jun 2011 12:44:14 GMT

Hi,

so what is the correct way to upgrade
Alfresco 3.2r with Latin1 database to 3.4 with UTF-8 database?

Export Database from 3.2r with latin1 with mysqldump
install new 3.4d
import 3.2r into new MySQL with Latin1
convert the tables with


SELECT CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' convert to CHARACTER SET utf8 COLLATE utf8_bin;' ) FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'alfresco_utf8' AND TABLE_COLLATION = 'latin1_swedish_ci' ORDER BY TABLE_ROWS desc;
‍‍‍‍


SELECT CONCAT
( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;') FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'alfresco_utf8' AND COLLATION_NAME = 'latin1_swedish_ci'and IS_NULLABLE='NO'
      UNION
 SELECT CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;') FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA = 'alfresco_utf8' AND COLLATION_NAME = 'latin1_swedish_ci'and IS_NULLABLE='YES';
‍‍‍‍‍‍‍‍

check the database connect Config includes UTF8

move in old alf_data and set rindex=full?

are the all information modified to UTF-8?

best regards
thomas