Converting your MySQL database to UTF8 - Revision history

Gmads: Steps for Windows

2012-03-02T09:53:54Z

Steps for Windows

← Older revision		Revision as of 09:53, 2 March 2012
Line 87:		Line 87:

	====Explained====		====Explained====
	The following steps will guide you in creating a database ~~dumb~~, editing the database dump so that the correct charset and collation are used and then restoring the new database.		The following steps will guide you in creating a database dump, editing the database dump so that the correct charset and collation are used and then restoring the new database.

	To start please open a new terminal and move to a temp directory.		To start please open a new terminal and move to a temp directory.
Line 141:		Line 141:

	===Windows===		===Windows===
	~~Could someone who is familiar with Windows please convert~~ the above ~~into something~~ that ~~will work on Windows?~~		The following steps will let you create a database dump, edit it so that the correct charset (utf8) and collation (utf8_unicode_ci) are used, and then restore the new database. For this, you'll first have to download [http://sed.sourceforge.net/grabbag/ssed/sed-3.62.zip Super Sed (Win32 executable, zipped)].
	~~There are likely~~ several ~~additional arguments for mysqldump you will need to specify including setting~~ the file ~~for output~~ using -r to ~~avoid~~ newline ~~issues~~.
			Start by opening a command window and move to a temporary folder.

			[your mysql dir]\bin\mysqldump -u [username] -p[password] -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B [dbname] -r dbdump.sql
			ssed -e "s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci/" dbdump.sql \| ssed -e "s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/" >dbdump_w.sql
			[your mysql dir]\bin\mysql -u [username] -p[password] [dbname] < dbdump_w.sql

			====mysqldump parameters====

			; username : Username used to access your database.
			; password : Password for the above user.
			; -c (--complete-insert) : Creates INSERT statements that include column names.
			; -e (--extended-insert) : Uses a multiple-row INSERT syntax that includes several VALUES lists; this results in a smaller dump file and speeds up inserts when the file is reloaded.
			; --default-character-set=utf8 : Use and set '''utf8''' as the default character set.
			; --single-transaction : This option is useful only when using transactional tables such as InnoDB, as it helps minimize the locking time duration.
			; --skip-set-charset : Suppress the SET NAMES statement (not needed because it is going to be changed).
			; --add-drop-database : Adds a DROP DATABASE statement before each CREATE DATABASE statement (to restore over the top of our existing database).
			; --add-drop-table : Adds a DROP TABLE statement before each CREATE TABLE statement.
			; -B (--databases) : Defines the name of the database to convert.
			; -r (--result-file) : This option should be used on Windows to prevent newline “\n” characters from being converted to “\r\n” carriage return/newline sequences. The result file is created and its previous contents overwritten, even if an error occurs while generating the dump.

	===Special chars won't import correctly===		===Special chars won't import correctly===

Tsala: Installing Moodle template

2012-02-13T08:38:39Z

Installing Moodle template

← Older revision		Revision as of 08:38, 13 February 2012
Line 1:		Line 1:
			{{Installing Moodle}}
	This document describes how to convert your MySQL database from the latin1 charset to UTF8. Moodle requires that your Database is now UTF8 and will not upgrade if your database is not.		This document describes how to convert your MySQL database from the latin1 charset to UTF8. Moodle requires that your Database is now UTF8 and will not upgrade if your database is not.

Andreabix: /* Special chars won't import correctly */

2011-08-05T15:23:24Z

Special chars won't import correctly

← Older revision		Revision as of 15:23, 5 August 2011
Line 148:		Line 148:
	# cd /folder_where_your_dump_is/		# cd /folder_where_your_dump_is/
	# mysql -u your_user -p		# mysql -u your_user -p
	> create database yourdb charset=~~uf8~~;		> create database yourdb charset=utf8;
	> use yourdb;		> use yourdb;
	> SET NAMES 'utf8';		> SET NAMES 'utf8';

Andreabix at 15:22, 5 August 2011

2011-08-05T15:22:34Z

@@ Line 142: / Line 142: @@
 Could someone who is familiar with Windows please convert the above into something that will work on Windows?
 There are likely several additional arguments for mysqldump you will need to specify including setting the file for output using -r to avoid newline issues.
 ==More information==

Hostmaked: /* Windows */

2011-02-17T08:37:38Z

Windows

@@ Line 142: / Line 142: @@
 Could someone who is familiar with Windows please convert the above into something that will work on Windows?
 There are likely several additional arguments for mysqldump you will need to specify including setting the file for output using -r to avoid newline issues.
 ==More information==

Hostmaked: /* Windows */

2011-02-17T08:36:08Z

Windows

← Older revision		Revision as of 08:36, 17 February 2011
Line 143:		Line 143:
	There are likely several additional arguments for mysqldump you will need to specify including setting the file for output using -r to avoid newline issues.		There are likely several additional arguments for mysqldump you will need to specify including setting the file for output using -r to avoid newline issues.

	I had the same problem with converting DB from latin1 to utf8. And that how I did it:
			== I had the same problem with converting DB from latin1 to utf8. And that how I did it:

	1. Export your DB to somedb.sql on your pc.		1. Export your DB to somedb.sql on your pc.
Line 154:		Line 155:
	7. Upload(import) new dump to server.		7. Upload(import) new dump to server.
	8. Check your site		8. Check your site
	Done.		Done. ==

	==More information==		==More information==

Hostmaked: /* Windows */

2011-02-17T08:34:34Z

Windows

@@ Line 142: / Line 142: @@
 Could someone who is familiar with Windows please convert the above into something that will work on Windows?
 There are likely several additional arguments for mysqldump you will need to specify including setting the file for output using -r to avoid newline issues.
 ==More information==

T100ss: /* Default Mysql character set */

2011-01-26T13:15:00Z

Default Mysql character set

← Older revision		Revision as of 13:15, 26 January 2011
Line 55:		Line 55:
	--skip-character-set parameter, will restore the database with your new default character set of utf8.		--skip-character-set parameter, will restore the database with your new default character set of utf8.

	utf8_unicode and utf8_general are not the same but similar. The difference is described in the ~~mysql~~ documentation page.		utf8_unicode and utf8_general are not the same but similar. The difference is described in the unicode documentation page. (Link below)

	==Converting an empty database==		==Converting an empty database==

T100ss: /* Default Mysql character set */

2011-01-26T13:14:05Z

Default Mysql character set

← Older revision		Revision as of 13:14, 26 January 2011
Line 55:		Line 55:
	--skip-character-set parameter, will restore the database with your new default character set of utf8.		--skip-character-set parameter, will restore the database with your new default character set of utf8.

	utf8_unicode and utf8_general are not the same but similar.		utf8_unicode and utf8_general are not the same but similar. The difference is described in the mysql documentation page.
	The ~~mysql site suggests that the main~~ difference is ~~that unicode does a better job~~
	~~when it comes to sorting and dealing with strange characters whereas general performs better~~
	in ~~terms of speed.~~

	~~Someone who really knows what they are talking about on collations might edit this please~~.

	==Converting an empty database==		==Converting an empty database==

T100ss: /* Default Mysql character set */

2011-01-26T10:31:12Z

Default Mysql character set

← Older revision		Revision as of 10:31, 26 January 2011
Line 55:		Line 55:
	--skip-character-set parameter, will restore the database with your new default character set of utf8.		--skip-character-set parameter, will restore the database with your new default character set of utf8.

	~~utf_unicode~~ and ~~utf_general~~ are not the same but similar.		utf8_unicode and utf8_general are not the same but similar.
	The mysql site suggests that the main difference is that unicode does a better job		The mysql site suggests that the main difference is that unicode does a better job
	when it comes to sorting and dealing with strange characters whereas general performs better		when it comes to sorting and dealing with strange characters whereas general performs better