Setting new database collation

[Waning Gibbous]

I installed XAMPP and I?m using phpMyAdmin to setup my local service. Right after choosing the database name I need to choose collation. The collation list is huge. Is what I choose there important? Does it need to be the same as what the hosting database is when I upload?

 

Neither the quick start guide nor the installation PDF mentions anything about this.

 

Thanks in advance.

[natewlew]

I think the reason you have to do that is because you a using mysql 5. I think the default might be latin1_swedish_ci? Try some out and see if it works?

[♥Vger]

You need to be very careful about this. Lots of people are having problems with the MySQL default collation, because there are a variety of default collations and not just one.

 

XAMPP installs MySQL using utf8_general_ci

 

php.ini may say something different

 

phpMyAdmin may say something different.

 

Your browser will amost certainly use one of the Windows character sets.

 

Your online server may use something else e.g. latin1_swedish_ci (don't ask me why it's swedish_ci)

 

Best thing to do is this:

 

Make sure what your MySQL server is using by doing the following. Open the MS-DOS command prompt and put this into it:

mysql --help|grep default-character-set

 

Set php.ini and phpMyAdmin to use the same character set as returned by using that command.

 

Download and install a plain text editor called unipad. Open the default oscommerce.sql file in UniPad and see if any 'foreign' charcters are not displayed correctly - look especially at the 'foreign' Zones (states in countries like Germany, Austria and Spain). If you find any question marks replacing letters or odd characters replacing letters then replace them with the originals using UniPad. You'll now have a file that complies with the character set you have in use with php, mysql and phpmyadmin.

 

Vger

[Waning Gibbous]

Best thing to do is this:

 

Make sure what your MySQL server is using by doing the following. Open the MS-DOS command prompt and put this into it:

mysql --help|grep default-character-set

 

I would have used the default settings originally but it doesn?t look like any defaults are set. In the window it just says collation.

 

 

I?m getting an error message. Apache and MySQL are running according to XAMPP Control Panel.

 

When running Vger's code from the command prompt window:

mysql --help|grep default-character-set

 

I get this error:

?mysql? is not recognized as an internal or external command, operable program or batch file.

 

So what didn?t I do right?

 

Just so I understand this? I build a database on my computer. When I upload it to the server it doesn?t need to have the same collation?

[Waning Gibbous]

Download and install a plain text editor called unipad. Open the default oscommerce.sql file in UniPad and see if any 'foreign' charcters are not displayed correctly - look especially at the 'foreign' Zones (states in countries like Germany, Austria and Spain). If you find any question marks replacing letters or odd characters replacing letters then replace them with the originals using UniPad. You'll now have a file that complies with the character set you have in use with php, mysql and phpmyadmin.

 

Sorry about double post. I installed UniPad and took a look at my oscommerce.sql file. Here is just part of one line. There are dozens of lines like this. How do I know what to put in place of those question marks?

 

 

INSERT INTO products_description VALUES (1,2,'Matrox G200 MMS','<b>Unterst�tzung f�r zwei bzw. vier analoge oder digitale Monitore</b><br><br>\r\nDie Matrox G200 Multi-Monitor-Serie f�hrt die bew�hrte Matrox Tradition im Multi-Monitor- Bereich fort und bietet flexible und fortschrittliche L�sungen.Matrox stellt als erstes Unternehmen einen vierfachen digitalen PanelLink� DVI Flachbildschirm Ausgang zur Verf�gung. Mit den optional erh�ltlichen TV Tuner und Video-Capture M�glichkeiten stellt die Matrox G200 MMS eine alles umfassende Mehrschirm-L�sung

[natewlew]

I have never ran mysql on a windows pc but I think you have to cd into the mysql directory before that command will work.

 

If you do get the mysql command to work you could build a database from command line.

(enter) = press the enter button

mysql -u root -p (enter)

password: (put your password here) (enter)

 

mysql> create database mydatabase; (enter)

 

mysql> use mydatabase; (enter)

 

mysql> GRANT ALL ON mydatabase.* TO username@localhost IDENTIFIED BY "mypassword"; (enter)

 

The commands come after mysql>

 

mydatabase is your database name.

 

username is whatever you want your username to be

 

mypassword is whatever you want your database password to be.

 

This works on Linux, it should be the same for windows?

[natewlew]

You could try something like:

C:\program files\apachefriends\xampp\mysql\bin\mysql -u root -p

 

Maybe that will let you into the mysql>

[Waning Gibbous]

Here is my path: D:\Database\xampp\mysql\bin

I was trying from c:\ and now after going to the right place I get this error:

 

?grep? is not recognized as an internal or external command, operable program or batch file.

 

So we are inching closer to the end of the line, any suggestions?

 

Nate your suggestions for setting up database using command prompt look really straight forward. But what started all this was collation. Where is that set at using the command prompt?

[natewlew]

If you do it from command line you might not have to tell it what collation. I create all my databases from command line. I had mysql 5 for a while I don't belive I had to tell it. I belive it made them Latin or something like that.

 

At least you found mysql. I don't know about grep.

[Waning Gibbous]

Here is what I was able to retrieve using: mysqld --verbose --help

 

default-character-set latin1

default-collation latin1_swedish_ci

 

Thanks for all your help. Now I?ll try to go on with what Vger was explaining earlier.

 

This is from the mysql website: mySQL Webpage

 

?By default, MySQL uses the latin1 (cp1252 West European) character set and the latin1_swedish_ci collation that sorts according to Swedish/Finnish rules. These defaults are suitable for the United States and most of Western Europe.

All MySQL binary distributions are compiled with --with-extra-charsets=complex. This adds code to all standard programs that enables them to handle latin1 and all multi-byte character sets within the binary. Other character sets are loaded from a character-set definition file when needed. ?