How do you import text from Croatian Latin

I am trying to import data from an HTML page into a MySQL table.

I have managed to get the HTML data into a delimited form in a Notepad file. From there I have had some success using LOAD DATA INFILE from the command line. But so far, I have not been able to import the data with the Latin Croatian characters intact. They are all translated or mangled. I have noticed that sometimes the LOAD function cannot even find the delimiters if the file type for the Notepad file is ASC-II. Other file types available for Notepad are UTF-8 and UNICODE. I have tried both with no success.

I am trying HeidiSQL in the hope that it might be able to make this possible. But when I use the "Import CSV file" function and open up my file when I look at the Character set option, I see that part greyed out with the text, "Unsupported by this server".

Am I up against some basic limitation of MySQL or is there some option that I can use to make just one column in my database Latin Croatian. (latin2, I believe it is) Can HeidiSQL help? Since it would appear that the character set is available, I can assume that it is possible once I know how.

How do I import data from Croatian Latin into a MySQL database table?

Any and all tips, clues and references would be appreciated.

Specifying CHARACTER SET in LOAD DATA queries requires MySQL 5.1.17 or later which you obviously don't run.

My first idea would be to
* use a local MySQL 5.1.44 or whatever GA release currently, to get the charset feature for LOAD DATA
* prepare the file in notepad, using UTF-8 for saving
* use HeidiSQL's import dialog, select UTF-8 and the desired table

Once you have the file imported into a local table, you can export this table as .sql file which only requires a minimum version of MySQL 4.1 then, or directly export it to the server where you originally wanted to import your file.

Wow, an upgrade? I'll try it and report back.

Thanks.

HeidiSQL is great. Even with the latest edition of MySQL Workbench, I still use the MySQL Query Browser. I may have a replacement for it in HeidiSQL.

Upgrading from MySQL 5.0 to MySQL 5.1 is a non trivial issue. I just spent a couple afternoons restoring my Windows system backups to bring back a mangled MySQL upgrade attempt.

Do not attempt it until you have read the MySQL Manual for 5.1 at the MySQL site:

http://dev.mysql.com/doc/refman/5.1/en/installing.html

http://dev.mysql.com/doc/refman/5.1/en/upgrading-downgrading.html

http://dev.mysql.com/doc/refman/5.1/en/upgrading.html

What was mangled? Never heard of such problems.

None of my clients would work for one thing.

What is your understanding of how to upgrade from 5.0 to 5.1? I assumed it would be nothing more than using the installer for 5.1, it would all be automatic. It wasn't. Despite the links I've found that I posted previously, I have yet to see a step by step procedure for doing it.

For Windows, I do see a basic procedure here:

http://dev.mysql.com/doc/refman/5.1/en/windows-upgrading.html

Don't know but I guess installing can mix up existing stuff, yes.

This is my dirty plan for upgrading on my local Windows (which is used for testing purposes):
1. Grab the minimalistic non-installer archive: http://www.mysql.com/downloads/mirror.php?id=387559#mirrors
2. Unzip it into c:\mysql-5.1.x\
3. Start shell and stop any old server by "net stop mysql" or killing the task via taskmanager
4. Start the new server: "c:\mysql-5.1.x\bin\mysqld"

Migrate data:
1. Create required databases
2. Copy table files I want to migrate from old data directory, directly from "c:\mysql-5.1.OLD\data\mydb\" to "c:\mysql-5.1.NEW\data\mydb\"

I'll bet your installation would work.

I finally managed to install 5.1 using the installer. I uninstalled 5.0 first. I finally found the data directory for 5.1 which has moved from the Program Files directory to the Applicaton Data for All Users.

But, I'm finding that when I try to access some of the tables from the copied data, I get errors saying the tables don't exist even when I can see the files in the new directory. And HeidiSQL can't find them either.

I wonder, would it be possible to reinstall 5.0 without corrupting the 5.1 installation? I think I can do this be giving one or the other instances a different name. What would access from the command line be like? Does the MySQL client at the command line use a certain "instance" as default? How would I access the other instance from HeidiSQL, for example?

I'm posting over at the MySQL forums to no avail. I'm sorry I have to take my problems here. Thanks again for your input.

You can install more than one MySQL instance however that doesn't help you to solve your problem so I'm wondering you are going to waste even more time. Anyway, different instances cannot run at the same time, unless they run on a different port, where the default is 3306. Just give the second instance a 3307 in the my.ini . If you start MySQL via Windows service you'll also need a different service name than the default "mysql". You can register a MySQL service with a custom name using

c:\mysql5.1.x\bin\mysqld.exe --install MySQL5.1

Here is what appears to be working.

I reinstalled 5.0 using the windows installer and used the configuration wizard to move the port to 3307. During the installation, I chose "custom" and did not install anything but the server itself, leaving the client and the rest to new versions. We'll see how that works out. Also I did not check off the Paths Setting to allow the command line utilities, and left that to the 5.1 installation. This appears to work leaving my 5.1 available from the command line.

If I were to do it all over again, I would have left my 5.0 installed, used the Instance Configuration Wizard to move the 5.0 port to 3307 and, in general, allowed the 5.1 installation to take over while using the Instance Configuration Wizard part of the install. During the installation, the Instance Configuration Wizard launches automatically, but always remember, it is always available just in case you forget to set up the command line and anything else.

I've checked and was able to access my 5.0 using Heidi and so now consider myself ready to attack migrating databases and confronting my basic problem of applying 5.1's ability to apply my character set.

Success!

I've managed to import a delimited text file as according to anse's instructions in the second post. But, in addition to those instructions, the column expected to hold the Latin Croatian text, at least, should be set to the latin2-croatian-ci collation. It didn't work without that setting for me.