MySQL ODBC Connector - Exporting utf-8 data problems

Author

Exporting utf-8 data problems

Dave M G

2006-01-03, 1:23 pm

MySQL List,

I have recently switched over from Windows to Ubuntu Linux, in order to
emulate as much as possible the environment I have on my web hosting
service. The goal is to be able to develop and test my web sites more
completely at home before uploading them.

I have successfully installed Apache, PHP, and MySQL (Most of which
came by default when installing Ubuntu). I'm comfortable writing PHP and
MySQL code in a web page, but I am very much a beginner in MySQL set up
and maintenance.

I have all the PHP and HTML files downloaded, and now my next step is
to copy the databases from my web hosting service to my home machine.

A lot of my database data is bilingual, English and Japanese. I try at
every turn to store and retrieve all data in UTF-8 format.

Using phpMyAdmin on my virtual hosting service, I exported my database
information to a text file, which I then opened on my local machine,
again through the phpMyAdmin interface.

It mostly worked. All the tables and their contents were inserted into
the home version of the database.

However, when viewing the web pages where content is dynamically called
from the database, all the Japanese text appears on my home machine as a
series of question marks.

So far as I know, I selected to use utf-8 encoding at every available
opportunity. I'm wondering if the problems came when saving to a plain
text file.

Can anyone recommend the best way to preserve text encoding methods
when copying a database from one machine to another?

Any advice is much appreciated.

Thank you.

--
Dave M G

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Thomas Spahni

2006-01-04, 7:23 am

Dave,

what is the result of

prompt> set | grep LANG

? I suspect your problem is not within MySQL. Did you look at your
testfile using a editor?

Thomas Spahni

On Wed, 4 Jan 2006, Dave M G wrote:

> MySQL List,
>
> I have recently switched over from Windows to Ubuntu Linux, in order to
> emulate as much as possible the environment I have on my web hosting
> service. The goal is to be able to develop and test my web sites more
> completely at home before uploading them.
>
> I have successfully installed Apache, PHP, and MySQL (Most of which
> came by default when installing Ubuntu). I'm comfortable writing PHP and
> MySQL code in a web page, but I am very much a beginner in MySQL set up
> and maintenance.
>
> I have all the PHP and HTML files downloaded, and now my next step is
> to copy the databases from my web hosting service to my home machine.
>
> A lot of my database data is bilingual, English and Japanese. I try at
> every turn to store and retrieve all data in UTF-8 format.
>
> Using phpMyAdmin on my virtual hosting service, I exported my database
> information to a text file, which I then opened on my local machine,
> again through the phpMyAdmin interface.
>
> It mostly worked. All the tables and their contents were inserted into
> the home version of the database.
>
> However, when viewing the web pages where content is dynamically called
> from the database, all the Japanese text appears on my home machine as a
> series of question marks.
>
> So far as I know, I selected to use utf-8 encoding at every available
> opportunity. I'm wondering if the problems came when saving to a plain
> text file.
>
> Can anyone recommend the best way to preserve text encoding methods
> when copying a database from one machine to another?
>
> Any advice is much appreciated.
>
> Thank you.
>
> --
> Dave M G

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Gleb Paharenko

2006-01-04, 7:23 am

Hello.

>However, when viewing the web pages where content is dynamically called
> from the database, all the Japanese text appears on my home machine as
>a series of question marks.

You should localize the problem. Check with the text editor that files,
which you have got from phpMyAdmin really contain data in UTF8 encoding.
Do not use phpAdmin until you completely understand what's going on. Use
mysql command line client to import your databases. After you have done
the import, check with some client that the data in MySQL is still
correct and in UTF8 (use GUIs like MySQL Query Browser in case your
terminal doesn't support unicode, and you can't use mysql command line
client). Check the settings of you server with:
show variables like '%char%';
statement. Read:
http://dev.mysql.com/doc/refman/5.0/en/charset.html

Dave M G wrote:
> MySQL List,
>
> I have recently switched over from Windows to Ubuntu Linux, in order to
> emulate as much as possible the environment I have on my web hosting
> service. The goal is to be able to develop and test my web sites more
> completely at home before uploading them.
>
> I have successfully installed Apache, PHP, and MySQL (Most of which
> came by default when installing Ubuntu). I'm comfortable writing PHP and
> MySQL code in a web page, but I am very much a beginner in MySQL set up
> and maintenance.
>
> I have all the PHP and HTML files downloaded, and now my next step is
> to copy the databases from my web hosting service to my home machine.
>
> A lot of my database data is bilingual, English and Japanese. I try at
> every turn to store and retrieve all data in UTF-8 format.
>
> Using phpMyAdmin on my virtual hosting service, I exported my database
> information to a text file, which I then opened on my local machine,
> again through the phpMyAdmin interface.
>
> It mostly worked. All the tables and their contents were inserted into
> the home version of the database.
>
> However, when viewing the web pages where content is dynamically called
> from the database, all the Japanese text appears on my home machine as a
> series of question marks.
>
> So far as I know, I selected to use utf-8 encoding at every available
> opportunity. I'm wondering if the problems came when saving to a plain
> text file.
>
> Can anyone recommend the best way to preserve text encoding methods
> when copying a database from one machine to another?
>
> Any advice is much appreciated.
>
> Thank you.
>
> --
> Dave M G
>
>

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@ensita.net
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Dave M G

2006-01-04, 11:23 am

Thank you for the advice.

In order to isolate the problem, I have checked to see if the text file
that I have exported from the MySQL server on my virtual host is, in
fact, in UTF-8.
By opening it in OpenOffice and selecting UTF-8 encoding, it displays
correctly.
Not entirely without problems. Most of the Japanese text shows up
correctly. But about 10% of it shows like this:
プル・リーフで
I believe this is because there is probably some Japanese text that was
entered in sometime before the server upgraded MySQL to 4.1.
But, ignoring the 10% of "legacy" text, it seems that at the very
least, I do have a proper UTF-8 encoded text file with which to import
into my home MySQL server.

Instead of importing the data as an SQL file (which successfully
imported, but with faulty Japanese characters), I copied the text and
pasted them in as a straight SQL query.

But it returns an error. Can anyone enlighten me as to why the file
would import into SQL as an SQL file, but the text won't work as an
import statement?

Here is the error output:

SQL query:

# phpMyAdmin MySQL-Dump
# version 2.3.3pl1
# http://www.phpmyadmin.net/ (download page)
#
# Host: localhost
# Generation Time: Jan 04, 2006 at 10:04 AM
# Server version: 3.23.37
# PHP Version: 4.3.11
# Database : `signup`
# --------------------------------------------------------
#
# Table structure for table `event_groups`
#
CREATE TABLE event_groups(

egid int( 11 ) NOT NULL AUTO_INCREMENT ,
GROUP int( 11 ) NOT NULL default '0',
event int( 11 ) NOT NULL default '0',
PRIMARY KEY ( egid )
) TYPE = MYISAM

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'group int(11) NOT NULL default '0',
event int(11) NOT NULL de

(the error message cuts abruptly, as shown here)

Any advice would be much appreciated.

--
Dave M G

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Lopez David E-r9374c

2006-01-04, 11:23 am

dave

I believe group is a reserved word. change to grp.

david=20

-----Original Message-----
From: Dave M G & #91;mailto:martin@au
totelic.com]=20
Sent: Wednesday, January 04, 2006 8:55 AM
To: mysql@lists.mysql.com
Subject: Re: Exporting utf-8 data problems

Thank you for the advice.

In order to isolate the problem, I have checked to see if the
text file that I have exported from the MySQL server on my virtual host
is, in fact, in UTF-8.
By opening it in OpenOffice and selecting UTF-8 encoding, it
displays correctly.
Not entirely without problems. Most of the Japanese text shows
up correctly. But about 10% of it shows like this:
プル・リーフで
I believe this is because there is probably some Japanese text
that was entered in sometime before the server upgraded MySQL to 4.1.
But, ignoring the 10% of "legacy" text, it seems that at the
very least, I do have a proper UTF-8 encoded text file with which to
import into my home MySQL server.

Instead of importing the data as an SQL file (which successfully
imported, but with faulty Japanese characters), I copied the text and
pasted them in as a straight SQL query.=20

But it returns an error. Can anyone enlighten me as to why the
file would import into SQL as an SQL file, but the text won't work as an
import statement?

Here is the error output:

SQL query:

# phpMyAdmin MySQL-Dump
# version 2.3.3pl1
# http://www.phpmyadmin.net/ (download page) # # Host: localhost #
Generation Time: Jan 04, 2006 at 10:04 AM # Server version: 3.23.37 #
PHP Version: 4.3.11 # Database : `signup` #
--------------------------------------------------------
#
# Table structure for table `event_groups` # CREATE TABLE event_groups(

egid int( 11 ) NOT NULL AUTO_INCREMENT , GROUP int( 11 ) NOT NULL
default '0', event int( 11 ) NOT NULL default '0', PRIMARY KEY ( egid )
) TYPE =3D MYISAM=20

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'group int(11) NOT NULL default '0',
event int(11) NOT NULL de

(the error message cuts abruptly, as shown here)

Any advice would be much appreciated.

--
Dave M G

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? unsub...r
ola.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

mel list_php

2006-01-04, 11:23 am

Hi,
Maybe your problem is linked to phpmyadmin?
I was having issues when trying to use an 'old' version (2.5.7) because the
interface itself of phpmyadmin was set up as iso-8859-1, and that was
causing all my data to be entered as isolatin even if the tables were
declared as utf8.
By updating to 2.6.4 I've been able to set up the language in phpmyadmin as
en-utf-8, and now when I use this interface the data are actually utf8 in
the tables.
hth,
melanie

>From: Dave M G <martin@autotelic.com>
>To: mysql@lists.mysql.com
>Subject: Re: Exporting utf-8 data problems
>Date: Thu, 05 Jan 2006 00:54:44 +0900
>
>
> Thank you for the advice.
>
> In order to isolate the problem, I have checked to see if the text file
>that I have exported from the MySQL server on my virtual host is, in
>fact, in UTF-8.
> By opening it in OpenOffice and selecting UTF-8 encoding, it displays
>correctly.
> Not entirely without problems. Most of the Japanese text shows up
>correctly. But about 10% of it shows like this:
>プル・リーフで
> I believe this is because there is probably some Japanese text that was
>entered in sometime before the server upgraded MySQL to 4.1.
> But, ignoring the 10% of "legacy" text, it seems that at the very
>least, I do have a proper UTF-8 encoded text file with which to import
>into my home MySQL server.
>
> Instead of importing the data as an SQL file (which successfully
>imported, but with faulty Japanese characters), I copied the text and
>pasted them in as a straight SQL query.
>
> But it returns an error. Can anyone enlighten me as to why the file
>would import into SQL as an SQL file, but the text won't work as an
>import statement?
>
> Here is the error output:
>
>SQL query:
>
># phpMyAdmin MySQL-Dump
># version 2.3.3pl1
># http://www.phpmyadmin.net/ (download page)
>#
># Host: localhost
># Generation Time: Jan 04, 2006 at 10:04 AM
># Server version: 3.23.37
># PHP Version: 4.3.11
># Database : `signup`
># --------------------------------------------------------
>#
># Table structure for table `event_groups`
>#
>CREATE TABLE event_groups(
>
>egid int( 11 ) NOT NULL AUTO_INCREMENT ,
>GROUP int( 11 ) NOT NULL default '0',
>event int( 11 ) NOT NULL default '0',
>PRIMARY KEY ( egid )
> ) TYPE = MYISAM
>
>
>MySQL said: Documentation
>
>#1064 - You have an error in your SQL syntax; check the manual that
>corresponds to your MySQL server version for the right syntax to use
>near 'group int(11) NOT NULL default '0',
> event int(11) NOT NULL de
>
>(the error message cuts abruptly, as shown here)
>
> Any advice would be much appreciated.
>
>--
>Dave M G
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>http://lists.mysql.com/mysql? unsub...
il.co.uk
>

____________________
____________________
____________________
_____
The new MSN Search Toolbar now includes Desktop search!
http://toolbar.msn.co.uk/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links