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've been told that redirection via shell (e.g. < and > ) can (not always) affect the collation of a database (.e.g utf8). How can I import an export without redirecting so that I don't have to worry about this.

share|improve this question
    
Never heard of that before. Do you have some examples/proof for this? I don't think that redirection does something like that since *nix is completely aware of UTF8 and handles it as it should. –  DrColossos Apr 13 '11 at 15:30
    
@DrColossos, perhaps not correctly setting the code page in a Windows command-prompt could cause scrambling. –  Synetech May 3 '11 at 19:57
    
I keep meaning to go back and ask someone who understands the reason of what causes this... because I don't deal with it often enough. –  xenoterracide May 3 '11 at 20:06
add comment

3 Answers

Well, you could log into MySQL and do:

mysql> source myfile.sql

That takes the shell out of the picture altogether - but I would still recommend double-checking environment variables like NLS_LANG.

share|improve this answer
add comment

You may want to do this command on the import server

SHOW CREATE DATABASE dbname;

This will show you the default collation of the database you want to import into.

You can also change the import database's collation if needed.

UPDATE !!!

First of all, I would look at the what the OS says the character set is. On the Linux command line just run this:

echo ${LANG}

From the MySQL point of view

You could explicitly name the default character set in the mysqldump

--default-character-set (default is utf8)

You can see it in the mysql client:

mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Give it a Try !!!

As for collation, you may need to set one or more environment variables like these:

mysql> show variables like '%coll%';<BR>
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Set these explicitly upon export for mysqldump and import for mysql as follows:

mysqldump -h... -u... -p... --default-character-set=utf8 --triggers --routines source_db | mysql -h... -u... -p... --collation_connection=latin1_swedish_ci -A -Dtarget_db
share|improve this answer
1  
I don't think this answers the question completely. I think the question is about the wrong conversion while directing the output of the backup to a .sql file and not the change of the collation. So the problem seems to be in the export itself, not in the collation of the table. –  DrColossos Apr 14 '11 at 11:12
    
Updated my answer. –  RolandoMySQLDBA Apr 14 '11 at 17:08
1  
+1 I think the default-charset option is the important part and good explanation! –  DrColossos Apr 14 '11 at 17:28
    
@DrColossos +1 in Thanks for Looking over my Shoulder and Making Sure I answered the question contextually !!! –  RolandoMySQLDBA Apr 14 '11 at 17:31
    
;) Nevermind, I also learned something new! –  DrColossos Apr 14 '11 at 17:55
add comment

There's always the mysqlimport utility, however, your source would have to be delimited values... such as those obtained from a "SELECT * FROM ... INTO OUTFILE ..."

If you're moving large amounts of data, it might be faster to use this bulk loading method anyway.

Beware of the consequences it may have with replication however.

share|improve this answer
    
What about an export program? I agree that there should also be a way for an executable to directly read and write a source file, but then again, these come from *nix, where redirection and piping are much more frequently used than in Windows. –  Synetech May 3 '11 at 19:59
    
If you're looking for a GUI/Windows interface to work with, try TOAD for mysql. quest.com/toad-for-mysql –  randy melder May 4 '11 at 21:46
    
Command line tool are capable of directly reading/writeing files too. –  Synetech May 5 '11 at 1:58
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.