loading 0x00A0 into mysql
Hello,
I'm trying to make a copy of a mysql database and having an issue
loading fields containing a particular character. I've taken a dump
using mysqldump and then try to load it into a new database using
mysql --user=me test_database dump_file
Everything appears to work except that text fields containing a Unicode
non-breaking space (0x00A0) are truncated just before that character. I
can see the field in the dump file and it looks OK, but it doesn't all
make it into the new database.
My Unicode foo is not strong, and it's my first day back at work :( so
I'm hoping somebody recognises this problem immediately.
What I do know:
Server version: 5.5.34-0ubuntu0.12.04.1 (Ubuntu)
Extract from dump_file including offending character:
-- Table structure for table `text_for_pages`
--
DROP TABLE IF EXISTS `text_for_pages`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `text_for_pages` (
`page` text NOT NULL,
`keytext` text NOT NULL,
`value` text,
PRIMARY KEY (`page`(30),`keytext`(30))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `text_for_pages`
--
LOCK TABLES `text_for_pages` WRITE;
/*!4 ALTER TABLE `text_for_pages` DISABLE KEYS */;
INSERT IGNORE INTO `text_for_pages` (`page`, `keytext`, `value`) VALUES
('About','left-column','text value'),('About','main-column','some text
... A0 ... more text ...');
/*!4 ALTER TABLE `text_for_pages` ENABLE KEYS */;
The value created in the database is just 'some text ... '
Cheers, Dave
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: loading 0x00A0 into mysql
Thanks for the reply, and I apologize because I expect I've broken
threading. The list isn't mailing the posts to me, so I've nothing to
reply to. I've had to cut and paste from the web archive...
2014/01/06 12:18 +, Dave Howorth
Everything appears to work except that text fields containing a
Unicode non-breaking space (0x00A0) are truncated just before that
character. I can see the field in the dump file and it looks OK, but
it doesn't all make it into the new database.
Well, there are too many aspects to this, but the first is the
character set that mysql expects for input. If, say, it is USASCII
(note that between the character set that mysql takes for input and
the character set in the table no association is needful), the nbsp
is out of range.
Hmm, is there any way to tell what character set mysql expects, or
better yet to tell it what to read? Or can I tell mysqldump to encode
its output differently?
(I promise to RTFM, but want to get this question out there whilst I'm
reading!)
(It is, of course, not nice if mysqldump yields an output that
mysql cannot read.)
Indeed; I'd go so far as to call that a bug. But that does seem to be
what's happening.
Try entering it with some escape-sequence (this one is based on the
original SQL with features from PL1, not from C, which MySQL supports
if 'ANSI' is in sql_mode):
I don't understand the 'sql_mode', though I expect I can look that up
too. But I did try these:
'some text ... ' || X'A0' || ' ... more text ...'
causes the contents of the field to be '1'.
or (slightly less PL1)
CONCAT('some text ... ', X'A0', ' ... more text ...')
Produces the same effect as embedding the character directly. i.e. the
value of the field is truncated just before the problem character.
However, substituting for the character with the string 'nbsp;' does
allow mysql to read past it. I've now discovered that it also blows up
on some other characters with the top bit set such as 0x91. What's
strange about that is that they used to work. So my first thought now is
that something has changed recently. Perhaps an update to one of the
servers or clients involved? I don't remember changing anything in my
code, but I can't be absolutely sure.
Cheers, Dave
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
[Fwd: Fwd: MySQL segmentation faults...]
Jochen Wiedman asked that I post this stack trace.
Please cc me on any replies, as I'm not subscribed to the list.
The original problem description is at the end.
Cheers, Dave
Original Message
Subject: Re: MySQL segmentation faults...
Date: Thu, 18 Sep 2003 20:59:57 +0200
From: Jochen Wiedmann [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
References: [EMAIL PROTECTED]
[EMAIL PROTECTED] [EMAIL PROTECTED]
Excellent stack trace, post this to the MySQL mailing list. It is a bug
in the C driver.
Jochen
Dave Howorth wrote:
On the mysqlcc list Adam Hooper just wrote:
In general, running strace program will give some useful output;
it'll give developers a good idea of where the problem is. Beyond
that, a backtrace is useful. To produce one:
1. Run gdb program (gdb is available for EVERY distribution)
2. Type 'run'
3. Get a segfault. The window won't close, and gdb will pop up a
warning of sorts.
4. Type 'bt' and email the output here :).
This seems like it might be relevant to diagnosing my
DBD::mysql::db::_login crash, so in the hope it may help ...
The strace output is:
open(/var/lib/mysql/my.cnf, O_RDONLY|O_LARGEFILE) = 3
fstat64(3, {st_mode=S_IFREG|0644, st_size=48, ...}) = 0
old_mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS,
-1, 0) = 0x4001a000
read(3, # This file must exist - CAN-200..., 4096) = 48
read(3, , 4096) = 0
close(3)= 0
munmap(0x4001a000, 4096)= 0
open(/home/dhoworth/.my.cnf, O_RDONLY|O_LARGEFILE) = -1 ENOENT (No
such file or directory)
--- SIGSEGV (Segmentation fault) ---
+++ killed by SIGSEGV +++
The gdb output is:
Program received signal SIGSEGV, Segmentation fault.
0x401cc030 in strcend () from /usr/lib/libmysqlclient.so.10
(gdb) bt
#0 0x401cc030 in strcend () from /usr/lib/libmysqlclient.so.10
#1 0x401bd922 in simple_command () from /usr/lib/libmysqlclient.so.10
#2 0x401be31d in mysql_real_connect () from
/usr/lib/libmysqlclient.so.10
#3 0x4019ebd4 in mysql_dr_connect ()
from /usr/lib/perl5/auto/DBD/mysql/mysql.so
#4 0x4019ef40 in mysql_dr_connect ()
from /usr/lib/perl5/auto/DBD/mysql/mysql.so
#5 0x4019efb3 in mysql_db_login () from
/usr/lib/perl5/auto/DBD/mysql/mysql.so
#6 0x401a3114 in XS_DBD__mysql__db__login ()
from /usr/lib/perl5/auto/DBD/mysql/mysql.so
#7 0x0809c70c in Perl_pp_entersub ()
#8 0x08097090 in Perl_runops_standard ()
#9 0x0805cdca in perl_call_sv ()
#10 0x0805c981 in perl_call_sv ()
#11 0x401910d1 in XS_DBI_dispatch () from /usr/lib/perl5/auto/DBI/DBI.so
#12 0x0809c70c in Perl_pp_entersub ()
#13 0x08097090 in Perl_runops_standard ()
#14 0x0805c565 in perl_run ()
#15 0x0805c2cb in perl_run ()
#16 0x08059ca0 in main ()
#17 0x4005b0bf in __libc_start_main () from /lib/libc.so.6
HTH, Dave
==
Dave Howorth wrote:
I'm trying to use LOAD DATA and it is returning Warnings: 12. I can't
use SHOW WARNINGS because I'm using mysql 3.23.49 since I'm on
Debian/Woody. A search of mysql list archives showed up a Perl script
called load_diag.pl by Paul Dubois that diagnoses problems in data files
so I've downloaded that.
Sadly, it is crashing. When I run it like this:
~/bin/load_diag.pl -u dhoworth attributes attributes.txt
it reports Segmentation fault. When I run it in the debugger it
reports Aborted. By single-stepping, I've tracked the failure to this
call:
DBD::mysql::dr::connect(/usr/lib/perl5/DBD/mysql.pm:131):
131: DBD::mysql::db::_login($this, $dsn, $username, $password)
132: or $this = undef;
It aborts when I try to single-step into it.
perl is v5.6.1, examining the modules shows
# $Id: DBI.pm,v 11.7 2002/02/07 03:00:53 timbo Exp $
$DBI::VERSION = 1.21; # == ALSO update the version in the pod text
below!
# $Id: dbd.pm.in,v 1.6 1999/10/21 20:05:43 joe Exp $
$VERSION = '2.0416';
I've written a small Perl program to test my installation (see below)
and that works, as does the mysql client and mysqlcc, and I'm not sure
where to look next.
All suggestions welcome.
Thanks, Dave
===
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dsn = 'dbi:mysql:t1';
my $user = 'dhoworth';
my $password = '';
my $dbh = DBI-connect($dsn, $user, $password,
{ RaiseError = 1 });
my $sth = $dbh-prepare('SELECT * FROM regions');
$sth-execute();
while (my @row = $sth-fetchrow_array)
{
print @row\n;
}
--
Dave Howorth
MRC Centre for Protein Engineering
Hills Road, Cambridge, CB2 2QH
01223 252960
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: MySQL segmentation faults...
Jochen Wiedman asked that I post this stack trace.
Please cc me on any replies, as I'm not subscribed to the list.
The original problem description is at the end.
Cheers, Dave
Original Message
Subject: Re: MySQL segmentation faults...
Date: Thu, 18 Sep 2003 20:59:57 +0200
From: Jochen Wiedmann [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
References: [EMAIL PROTECTED]
[EMAIL PROTECTED] [EMAIL PROTECTED]
Excellent stack trace, post this to the MySQL mailing list. It is a bug
in the C driver.
Jochen
Dave Howorth wrote:
On the mysqlcc list Adam Hooper just wrote:
In general, running strace program will give some useful output;
it'll give developers a good idea of where the problem is. Beyond
that, a backtrace is useful. To produce one:
1. Run gdb program (gdb is available for EVERY distribution)
2. Type 'run'
3. Get a segfault. The window won't close, and gdb will pop up a
warning of sorts.
4. Type 'bt' and email the output here :).
This seems like it might be relevant to diagnosing my
DBD::mysql::db::_login crash, so in the hope it may help ...
The strace output is:
open(/var/lib/mysql/my.cnf, O_RDONLY|O_LARGEFILE) = 3
fstat64(3, {st_mode=S_IFREG|0644, st_size=48, ...}) = 0
old_mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS,
-1, 0) = 0x4001a000
read(3, # This file must exist - CAN-200..., 4096) = 48
read(3, , 4096) = 0
close(3)= 0
munmap(0x4001a000, 4096)= 0
open(/home/dhoworth/.my.cnf, O_RDONLY|O_LARGEFILE) = -1 ENOENT (No
such file or directory)
--- SIGSEGV (Segmentation fault) ---
+++ killed by SIGSEGV +++
The gdb output is:
Program received signal SIGSEGV, Segmentation fault.
0x401cc030 in strcend () from /usr/lib/libmysqlclient.so.10
(gdb) bt
#0 0x401cc030 in strcend () from /usr/lib/libmysqlclient.so.10
#1 0x401bd922 in simple_command () from /usr/lib/libmysqlclient.so.10
#2 0x401be31d in mysql_real_connect () from
/usr/lib/libmysqlclient.so.10
#3 0x4019ebd4 in mysql_dr_connect ()
from /usr/lib/perl5/auto/DBD/mysql/mysql.so
#4 0x4019ef40 in mysql_dr_connect ()
from /usr/lib/perl5/auto/DBD/mysql/mysql.so
#5 0x4019efb3 in mysql_db_login () from
/usr/lib/perl5/auto/DBD/mysql/mysql.so
#6 0x401a3114 in XS_DBD__mysql__db__login ()
from /usr/lib/perl5/auto/DBD/mysql/mysql.so
#7 0x0809c70c in Perl_pp_entersub ()
#8 0x08097090 in Perl_runops_standard ()
#9 0x0805cdca in perl_call_sv ()
#10 0x0805c981 in perl_call_sv ()
#11 0x401910d1 in XS_DBI_dispatch () from /usr/lib/perl5/auto/DBI/DBI.so
#12 0x0809c70c in Perl_pp_entersub ()
#13 0x08097090 in Perl_runops_standard ()
#14 0x0805c565 in perl_run ()
#15 0x0805c2cb in perl_run ()
#16 0x08059ca0 in main ()
#17 0x4005b0bf in __libc_start_main () from /lib/libc.so.6
HTH, Dave
==
Dave Howorth wrote:
I'm trying to use LOAD DATA and it is returning Warnings: 12. I can't
use SHOW WARNINGS because I'm using mysql 3.23.49 since I'm on
Debian/Woody. A search of mysql list archives showed up a Perl script
called load_diag.pl by Paul Dubois that diagnoses problems in data files
so I've downloaded that.
Sadly, it is crashing. When I run it like this:
~/bin/load_diag.pl -u dhoworth attributes attributes.txt
it reports Segmentation fault. When I run it in the debugger it
reports Aborted. By single-stepping, I've tracked the failure to this
call:
DBD::mysql::dr::connect(/usr/lib/perl5/DBD/mysql.pm:131):
131: DBD::mysql::db::_login($this, $dsn, $username, $password)
132: or $this = undef;
It aborts when I try to single-step into it.
perl is v5.6.1, examining the modules shows
# $Id: DBI.pm,v 11.7 2002/02/07 03:00:53 timbo Exp $
$DBI::VERSION = 1.21; # == ALSO update the version in the pod text
below!
# $Id: dbd.pm.in,v 1.6 1999/10/21 20:05:43 joe Exp $
$VERSION = '2.0416';
I've written a small Perl program to test my installation (see below)
and that works, as does the mysql client and mysqlcc, and I'm not sure
where to look next.
All suggestions welcome.
Thanks, Dave
===
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dsn = 'dbi:mysql:t1';
my $user = 'dhoworth';
my $password = '';
my $dbh = DBI-connect($dsn, $user, $password,
{ RaiseError = 1 });
my $sth = $dbh-prepare('SELECT * FROM regions');
$sth-execute();
while (my @row = $sth-fetchrow_array)
{
print @row\n;
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
apology for double post
Please accept my apologies for the double-post earlier. The system sent me a 'self-moderation' challenge requiring me to click on a link. When I did so, the page said there was no outstanding challenge and that I should send the message again. Sorry, Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
