convert mysql data latin1 to utf-8?
Moderator: wendhausen
- alavi nik
- Joomla! Explorer
- Posts: 344
- Joined: Wed Sep 20, 2006 7:48 pm
- Location: Tehran,Iran
- Contact:
convert mysql data latin1 to utf-8?
Hello
in joomla 1.0.X series,database was latin1
and saved data same : U…?آ±Uئ’?آ² Uâ€،Uâ€
but in joomla 1.5 have change,and saved good data
for example U…?آ±Uئ’?آ² Uâ€،U†==> ُسلام.تست
how may i convert mysql to utf8?and solved charsets? and chars?
com_migrator,done this work
but is just for content,users and ...
but is not for fully database
Thanks
in joomla 1.0.X series,database was latin1
and saved data same : U…?آ±Uئ’?آ² Uâ€،Uâ€
but in joomla 1.5 have change,and saved good data
for example U…?آ±Uئ’?آ² Uâ€،U†==> ُسلام.تست
how may i convert mysql to utf8?and solved charsets? and chars?
com_migrator,done this work
but is just for content,users and ...
but is not for fully database
Thanks
Joomfa Team(Joomla farsi) ==> http://joomfa.org
- infograf768
- Joomla! Master
- Posts: 19133
- Joined: Fri Aug 12, 2005 3:47 pm
- Location: **Translation Matters**
Re: convert mysql data latin1 to utf-8?
Hello Mostafa,
Just make a dump of the database choosing utf8 as encoding.
Then, to be sure, open the dump in an utf8 editor and check all is OK.
Just make a dump of the database choosing utf8 as encoding.
Then, to be sure, open the dump in an utf8 editor and check all is OK.
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group
- alavi nik
- Joomla! Explorer
- Posts: 344
- Joined: Wed Sep 20, 2006 7:48 pm
- Location: Tehran,Iran
- Contact:
Re: convert mysql data latin1 to utf-8?
Hello jeans
thanks
i going to phpmyadmin,export joomla 1.0.x series database.
then i open this file into notepad++
and id not good
Thanks jean
thanks
i going to phpmyadmin,export joomla 1.0.x series database.
then i open this file into notepad++
and id not good
Thanks jean
Joomfa Team(Joomla farsi) ==> http://joomfa.org
-
- Joomla! Apprentice
- Posts: 13
- Joined: Fri Mar 31, 2006 5:46 pm
Re: convert mysql data latin1 to utf-8?
Same problem here, how can I export as UTF-8?
-
- Joomla! Apprentice
- Posts: 33
- Joined: Fri Jun 09, 2006 7:44 pm
- Location: Belgium (Charleroi)
Re: convert mysql data latin1 to utf-8?
Hello,
Simply ... export the tables with phpmyadmin in SQL format. Edit the .sql file with Wordpad (or other editor), replace "latin1" with "utf8" and save the file. Then, import the file with phpmyadmin...
Simply ... export the tables with phpmyadmin in SQL format. Edit the .sql file with Wordpad (or other editor), replace "latin1" with "utf8" and save the file. Then, import the file with phpmyadmin...
Cordialement,
Yamadix
Yamadix
-
- Joomla! Apprentice
- Posts: 5
- Joined: Tue May 12, 2009 10:37 pm
Re: convert mysql data latin1 to utf-8?
@yamadix
thanx for the reply friend ... but there's a little problem ... my exported .sql backup from phpmyadmin doesn't show character sets for tables creation .. am i missing an option or so in phpmyadmin ??
thanx for the reply friend ... but there's a little problem ... my exported .sql backup from phpmyadmin doesn't show character sets for tables creation .. am i missing an option or so in phpmyadmin ??
-
- Joomla! Apprentice
- Posts: 33
- Joined: Fri Jun 09, 2006 7:44 pm
- Location: Belgium (Charleroi)
Re: convert mysql data latin1 to utf-8?
Hello,
In the SQL-Export, for each table, you must find this line :
In the SQL-Export, for each table, you must find this line :
If no, I am sorry ... I don't have an other response !ENGINE=MyISAM DEFAULT CHARSET=latin1;
Cordialement,
Yamadix
Yamadix
-
- Joomla! Apprentice
- Posts: 5
- Joined: Tue May 12, 2009 10:37 pm
Re: convert mysql data latin1 to utf-8?
all that i got is Type=MyISAM ... only. Anyway i'll give it a try with ENGINE=MyISAM DEFAULT CHARSET=latin1; instead of the Type may be it'll work
- Sulpher
- Joomla! Explorer
- Posts: 294
- Joined: Mon May 21, 2007 8:54 am
- Contact:
Re: convert mysql data latin1 to utf-8?
xenome, take in mind next SQL queries:
DB charset replacement: ALTER DATABASE `db_name` COLLATE utf8_general_ci
Table charset replacement: ALTER TABLE `table_name` COLLATE utf8_general_ci
Where db_name and table_name - you have to use the right data.
DB charset replacement: ALTER DATABASE `db_name` COLLATE utf8_general_ci
Table charset replacement: ALTER TABLE `table_name` COLLATE utf8_general_ci
Where db_name and table_name - you have to use the right data.
Joomla! extensions & ecommerce plugins – https://norrnext.com
Unofficial Joomla channel on Telegram - @joomlahub
Follow me on Twitter: @sulpher
Unofficial Joomla channel on Telegram - @joomlahub
Follow me on Twitter: @sulpher
- infograf768
- Joomla! Master
- Posts: 19133
- Joined: Fri Aug 12, 2005 3:47 pm
- Location: **Translation Matters**
Re: convert mysql data latin1 to utf-8?
This WILL NOT work in Joomla 1.5.xenome wrote:all that i got is Type=MyISAM ... only. Anyway i'll give it a try with ENGINE=MyISAM DEFAULT CHARSET=latin1; instead of the Type may be it'll work
1.5 is fully utf8.
The default charset does not need to be set in the dump if the database is correctly collated.
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group
-
- Joomla! Explorer
- Posts: 345
- Joined: Sun Jul 16, 2006 1:21 pm
Re: convert mysql data latin1 to utf-8?
Hi,
I got similar problem, but I can't edit my dump file because it's 400 MB. I used the iconv to convert the database dump to UTF8 from Latin1, when I import it to the Joomla 1.5.15 it shows garbage and changes the system info in Joomla from UTF8 to Latin 1, Any idea how can I fix this problem?
Thanks
I got similar problem, but I can't edit my dump file because it's 400 MB. I used the iconv to convert the database dump to UTF8 from Latin1, when I import it to the Joomla 1.5.15 it shows garbage and changes the system info in Joomla from UTF8 to Latin 1, Any idea how can I fix this problem?
Thanks
- emakki
- Joomla! Ace
- Posts: 1686
- Joined: Fri Dec 01, 2006 9:26 am
- Location: Centreville, Virginia
- Contact:
Re: convert mysql data latin1 to utf-8?
We have discussed this issue thoroughly in the Arabic forum and solve the issue. There are several things to put in mind like collision must be utf-8 for all fields as well. I used a script to do that for me and I attached it (use on your own risk and you must backup your database). go to this link:
http://forum.joomla.org/viewtopic.php?p=823601#p823601
rename the file to index.php and upload in a folder in your website. Finally, enter db info and it will change all necessary fields, tables and so on to utf-8
A trick I used as well for Arabic, I assume for Urdu it will work, is to insert the contents of your .sql file in an html page (as an article in the old joomla that used to work) then show the page online and it will show the sql code along with the correct text in your language. Copy it from the page and past in notepad++ and save as utf without BOM. Then use it to export to your db.
I hope this helps.
Good luck,
Zak
http://forum.joomla.org/viewtopic.php?p=823601#p823601
rename the file to index.php and upload in a folder in your website. Finally, enter db info and it will change all necessary fields, tables and so on to utf-8
A trick I used as well for Arabic, I assume for Urdu it will work, is to insert the contents of your .sql file in an html page (as an article in the old joomla that used to work) then show the page online and it will show the sql code along with the correct text in your language. Copy it from the page and past in notepad++ and save as utf without BOM. Then use it to export to your db.
I hope this helps.
Good luck,
Zak
Live and learn.. No matter what you earn..Money or knowledge.. From market or college..
_______
http://alhost.org هل تبحث عن إستضافة؟ مقارنة بين أفضل شركات الإستضافة
http://alhost.me دليل ومعاينة شركات الاستضافة العربية
_______
http://alhost.org هل تبحث عن إستضافة؟ مقارنة بين أفضل شركات الإستضافة
http://alhost.me دليل ومعاينة شركات الاستضافة العربية
-
- Joomla! Intern
- Posts: 56
- Joined: Wed Feb 24, 2010 2:14 am
Re: convert mysql data latin1 to utf-8?
Thanks for the suggestion. I will give it a try.emakki wrote:We have discussed this issue thoroughly in the Arabic forum and solve the issue. There are several things to put in mind like collision must be utf-8 for all fields as well. I used a script to do that for me and I attached it (use on your own risk and you must backup your database). go to this link:
http://forum.joomla.org/viewtopic.php?p=823601#p823601
rename the file to index.php and upload in a folder in your website. Finally, enter db info and it will change all necessary fields, tables and so on to utf-8
A trick I used as well for Arabic, I assume for Urdu it will work, is to insert the contents of your .sql file in an html page (as an article in the old joomla that used to work) then show the page online and it will show the sql code along with the correct text in your language. Copy it from the page and past in notepad++ and save as utf without BOM. Then use it to export to your db.
I hope this helps.
Good luck,
Zak
-
- Joomla! Explorer
- Posts: 345
- Joined: Sun Jul 16, 2006 1:21 pm
Re: convert mysql data latin1 to utf-8?
I finally did it. These are the steps:
--------------------------
steps:
1- Install 1.5 without sample data
2- Install com_mtwmigrator-0.2.1 on the new Joomla 1.5
3- Migrate the users and groups only using com_mtwmigrator-0.2.1!
4- Install the Comporfiler
5- Migrate the Comprofiler Plugin data com_mtwmigrator-0.2.1
6- Delete Jos_content, Jos_content_frontpage, Jos_content_rating tables on the new installations
7- Backup the UTF8 converted jos_content, Jos_content_frontpage, Jos_content_rating tables individually.
8- Restore the jos_content, Jos_content_frontpage, Jos_content_rating tables into the new site.
9- The Jos_content table on the new 1.5 has 2 additional fields. Insert them by using the following 2 commands from MyPHP Admin
ALTER TABLE jos_content ADD alias VARCHAR(255) AFTER title;
ALTER TABLE jos_content ADD metadata text AFTER hits;
10- Install the Jomcomment component
11- Migrate the jomcomment data using the com_mtwmigrator from withing the new Joomla 1.5
--------
Converstion of the database:
Drop the database and create a new UTF8 database:
mysql --user=xx--password=xxx--execute="DROP DATABASE XXX; CREATE DATABASE XXX CHARACTER SET utf8 COLLATE utf8_general_ci;"
Restore the database:
mysql --user=XXX --password=XXX --default-character-set=utf8 XXX < XXX
Change the character Set:
sudo sed -e 's/latin1/utf8/g' -i XXX
--------------------------
steps:
1- Install 1.5 without sample data
2- Install com_mtwmigrator-0.2.1 on the new Joomla 1.5
3- Migrate the users and groups only using com_mtwmigrator-0.2.1!
4- Install the Comporfiler
5- Migrate the Comprofiler Plugin data com_mtwmigrator-0.2.1
6- Delete Jos_content, Jos_content_frontpage, Jos_content_rating tables on the new installations
7- Backup the UTF8 converted jos_content, Jos_content_frontpage, Jos_content_rating tables individually.
8- Restore the jos_content, Jos_content_frontpage, Jos_content_rating tables into the new site.
9- The Jos_content table on the new 1.5 has 2 additional fields. Insert them by using the following 2 commands from MyPHP Admin
ALTER TABLE jos_content ADD alias VARCHAR(255) AFTER title;
ALTER TABLE jos_content ADD metadata text AFTER hits;
10- Install the Jomcomment component
11- Migrate the jomcomment data using the com_mtwmigrator from withing the new Joomla 1.5
--------
Converstion of the database:
Drop the database and create a new UTF8 database:
mysql --user=xx--password=xxx--execute="DROP DATABASE XXX; CREATE DATABASE XXX CHARACTER SET utf8 COLLATE utf8_general_ci;"
Restore the database:
mysql --user=XXX --password=XXX --default-character-set=utf8 XXX < XXX
Change the character Set:
sudo sed -e 's/latin1/utf8/g' -i XXX
-
- Joomla! Fledgling
- Posts: 4
- Joined: Mon Jun 14, 2010 7:12 pm
Re: convert mysql data latin1 to utf-8?
Thanks Joomlan,
I was stuck in this same UTF-8 problem for the last 3 days. So far your solution seems to work for me as well. Mostafa, yamadix, emakki .. thanks you guys too.
I was stuck in this same UTF-8 problem for the last 3 days. So far your solution seems to work for me as well. Mostafa, yamadix, emakki .. thanks you guys too.
Gradually trying to learn Joomla.
http://www.biotechnologyforums.com - for biotech students
http://www.weightism.org - a [* spam *] community
http://www.biotechnologyforums.com - for biotech students
http://www.weightism.org - a [* spam *] community
-
- Joomla! Fledgling
- Posts: 4
- Joined: Tue Feb 03, 2009 2:41 pm
- Location: localhost
- Contact:
Re: convert mysql data latin1 to utf-8?
Hello everybody!
I have the same problem.
I have website with 20MB database on Mambo 4.5.5 Stable [ Arpie ] 2-Feb-2007
The collation on mysql database is latin1_swedish_ci. The charset in index.php is windows-1251
I create a new website with joomla 1.5.18 with encoding and collation UTF8.
So , I try to convert the database from latin1 to utf8 with notepad++ , but not work - the cyrillic symbols are not correct.
All database from mambo is latin1_swedish_ci - mos_users, mos_content and etc.
This tips posted in this topic not work for cyrillic. :-(
Can you have a idea how can I migrate from Mambo 4.5.5 to Joomla with different collation in database and different charset in the templates?
I have the same problem.
I have website with 20MB database on Mambo 4.5.5 Stable [ Arpie ] 2-Feb-2007
The collation on mysql database is latin1_swedish_ci. The charset in index.php is windows-1251
I create a new website with joomla 1.5.18 with encoding and collation UTF8.
So , I try to convert the database from latin1 to utf8 with notepad++ , but not work - the cyrillic symbols are not correct.
All database from mambo is latin1_swedish_ci - mos_users, mos_content and etc.
This tips posted in this topic not work for cyrillic. :-(
Can you have a idea how can I migrate from Mambo 4.5.5 to Joomla with different collation in database and different charset in the templates?
- infograf768
- Joomla! Master
- Posts: 19133
- Joined: Fri Aug 12, 2005 3:47 pm
- Location: **Translation Matters**
Re: convert mysql data latin1 to utf-8?
Have you tried to first migrate from Mambo to Joomla 1.0 and THEN migrate to 1.5?
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group
-
- Joomla! Fledgling
- Posts: 4
- Joined: Tue Feb 03, 2009 2:41 pm
- Location: localhost
- Contact:
Re: convert mysql data latin1 to utf-8?
infograf768 wrote:Have you tried to first migrate from Mambo to Joomla 1.0 and THEN migrate to 1.5?
Thank you!
First I update from Mambo to Joomla 1.0.15 Stable
After Update from Joomla 1.0.15-Stable to Joomla 1.5.18
Thank you for the reply!
Best regards!
-
- Joomla! Fledgling
- Posts: 2
- Joined: Fri Mar 16, 2012 4:11 am
Re: convert mysql data latin1 to utf-8?
I'm looking to go from 1.0.13 to 1.6 by way of going to 1.5 first.infograf768 wrote:Have you tried to first migrate from Mambo to Joomla 1.0 and THEN migrate to 1.5? Law of Attraction
I went down the long struggling road of getting Joomla, Coppermine and SMF integrated with a single login - handled by SMF (Joomlahacks).
I'm curious...will I lose these bridges while processing my upgrade to 1.5, or to 1.6 for that matter?
After much Research, i found:
No extensions available for anything that I have...I might have to stick with 1.0.13...too much stuff to sacrifice to make the move to 1.5...unless, there is a better migration script that does everything
Last edited by jasonsamual on Mon Mar 19, 2012 6:38 pm, edited 1 time in total.
-
- Joomla! Fledgling
- Posts: 1
- Joined: Mon Mar 19, 2012 12:40 pm
Re: convert mysql data latin1 to utf-8?
joomlan wrote:I finally did it. These are the steps:
--------------------------
steps:
1- Install 1.5 without sample data
2- Install com_mtwmigrator-0.2.1 on the new Joomla 1.5
3- Migrate the users and groups only using com_mtwmigrator-0.2.1!
.
.
.
Change the character Set:
sudo sed -e 's/latin1/utf8/g' -i XXX
Working several hours. Trying to resolve this problem.
This method work for me.
S. Bush
***** Manual signatures are NOT allowed *****_____
My personal website
http://www.ecerdc.com
Last edited by swervebush on Fri Mar 23, 2012 8:57 am, edited 3 times in total.
-
- Joomla! Fledgling
- Posts: 2
- Joined: Sat Apr 07, 2012 3:19 am
Re: convert mysql data latin1 to utf-8?
If you are running language that not English. I recommend to check if it's UTF-8 or not. As UTF-8 support almost every language.
- infograf768
- Joomla! Master
- Posts: 19133
- Joined: Fri Aug 12, 2005 3:47 pm
- Location: **Translation Matters**
Re: convert mysql data latin1 to utf-8?
Jean-Marie Simonet / infograf
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group
---------------------------------
ex-Joomla Translation Coordination Team • ex-Joomla! Production Working Group
-
- Joomla! Apprentice
- Posts: 6
- Joined: Tue Sep 13, 2011 7:07 am
Re: convert mysql data latin1 to utf-8?
Actually pretty valuable concoction of encoding tricks. I've been searching around google for this solution. But I would like to share how I did it with a littly different way of steps, including a SED script to whack all those weird characters back into line, and the necessary adjustments for your MYSQL server.
-
- I've been banned!
- Posts: 33
- Joined: Tue Jan 15, 2013 7:06 am
Re: convert mysql data latin1 to utf-8?
ihave this problem too
i use a script that load the data from my database that have unicode latin
and then i instert in database with utf-8 unicode
i use a script that load the data from my database that have unicode latin
and then i instert in database with utf-8 unicode