Bug #10903 The '–'(not '-') , encoded in GB2312 `A843`, will make SQL malfunction.
Submitted: 27 May 2005 6:35 Modified: 18 Aug 2005 8:06
Reporter: liuake liuake Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:mysql-4.1.12 OS:Linux (Linux)
Assigned to: Antony Curtis CPU Architecture:Any

[27 May 2005 6:35] liuake liuake
Description:
In a table, which has a `longtext` field name `content`.
Where i invoked a insert query, `content` value has a chinese character '–' (encoded as 'A843'), the query will work, and no error reported.
but when i select out it the result, it just not as i want!  All the characters after '–' in `content` field is trimed!

Maybe i need a ddemonstratipn.
well.
i use WordProcess Blog system(in table wp-posts, field `content` is longtext), and i post it a article with the content:

`zcat  lnx_920_disk1.cpio.gz | cpio –idmv
zcat  lnx_920_disk1.cpio.gz | cpio –idmv
zcat  lnx_920_disk1.cpio.gz | cpio –idmv`

after post, i find the content is indeed like this:
`zcat  lnx_920_disk1.cpio.gz | cpio `

i has checked the mysqld's log, the SQL string is just right. and i try this insert in PhhpMyAdmin, the same result it is.

After some testing. i find out it's course, the characters '–' (gb2312,not '-')

below information maybe useful, you can email me if you want more information.
thanks for your helping.
Configuration:
#CC=gcc CXX=gcc ./configure --prefix=/usr/local/mysql --with-charset=gb2312 --with-collation=gb2312_chinese_ci

Detail OS information:
Linux version 2.6.10-1-686 (dilinger@mouth) (gcc version 3.3.5 (Debian 1:3.3.5-8))

MySQL Variables: (copy from PhpMyAdmin)
binlog cache size   	 32768  	 32768
bulk insert buffer size 	8388608 	8388608
character set client 	gb2312 	gb2312
character set connection 	gb2312 	gb2312
character set database 	gb2312 	gb2312
character set results 	gb2312 	gb2312
character set server 	gb2312 	gb2312
character set system 	utf8 	utf8

How to repeat:
every time i use the character '–'
[27 May 2005 6:48] Jan Lindström
Could you please provide SHOW CREATE TABLE for a table used in your query and
example SQL query where you have a malfunction. Please provide also your mysql
config file.
[27 May 2005 6:50] Heikki Tuuri
Hi!

Please test also with a MyISAM type table. This bug is probably not InnoDB-specific.

Regards,

Heikki
[27 May 2005 8:36] liuake liuake
show create table

 wp_posts |CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `post_author` int(4) NOT NULL default '0',
  `post_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_category` int(4) NOT NULL default '0',
  `post_excerpt` text NOT NULL,
  `post_status` enum('publish','draft','private','static','object') NOT NULL default 'publish',
  `comment_status` enum('open','closed','registered_only') NOT NULL default 'open',
  `ping_status` enum('open','closed') NOT NULL default 'open',
  `post_password` varchar(20) NOT NULL default '',
  `post_name` varchar(200) NOT NULL default '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content_filtered` text NOT NULL,
  `post_parent` int(11) NOT NULL default '0',
  `guid` varchar(255) NOT NULL default '',
  `menu_order` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `post_name` (`post_name`),
  KEY `post_status` (`post_status`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312 |
[27 May 2005 8:37] liuake liuake
the insert sql. maybe too long. i what i get from msyqld's log.

INSERT INTO wp_posts
			(ID, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt,  post_status, comment_status, ping_status, post_password, post_name, to_ping, post_modified, post_modified_gmt, post_parent, menu_order)
			VALUES
			('28', '1', '2005-05-27 13:22:58', '2005-05-27 05:22:58', '在linux AS3.0上安装ORACLE9.2.04
一、 下载下载 Sun\'s Java Development Kit (JDK 1.4.2).
j2sdk-1_3_1_09-linux-i586.rpm.bin
下载Oracle 安装软件  otn.oracle.com.
lnx_920_disk1.cpio.gz
lnx_920_disk1.cpio.gz
lnx_920_disk1.cpio.gz
下载 Oracle 9.2.0.4.0 补丁包(3006854, 3095277)metalink.oracle.com.
p3095277_9204_LINUX.zip
p3006854_9204_LINUX.zip

二、解压文件
解压oracle文件
第一种方法:
zcat  lnx_920_disk1.cpio.gz | cpio –idmv
zcat  lnx_920_disk1.cpio.gz | cpio –idmv
zcat  lnx_920_disk1.cpio.gz | cpio –idmv
第二种方法
第一步:
gunzip lnx_920_disk1.cpio.gz
gunzip lnx_920_disk2.cpio.gz
gunzip lnx_920_disk3.cpio.gz
第二步:
cpio -idmv < lnx_920_disk1.cpio
cpio -idmv < lnx_920_disk2.cpio
cpio -idmv < lnx_920_disk3.cpio
解压ORACLE PATCH文件
mkdir 92040
unzip -d 92040 p3095277_9204_LINUX.zip
cd 92040
cpio -idmv < 9204_lnx32_release.cpio
三、设置核心参数
下面的表是最小的核心参数设置.

Parameter     SEMMNI   SEMMNS  SEMOPM  SEMMSL  SHMMAX   SHMMIN
Min Setting      100      256         100        100     2147483648     1
SHMMNI  SHMSEG   SHMVMX
100         4096         32767

有两种方法设置内核参数
1、
用以下命令查看参数的设置
# cat /proc/sys/kernel/sem
250 32000 32 128  
设置参数
# echo SEMMSL_value SEMMNS_value SEMMSL_value SEMOPM_value SEMMNI_value >proc/sys/kernel/sem                                                    
#  echo 250 32000 100 128 > /proc/sys/kernel/sem
共享内存设置
echo 2147483648 >  /proc/sys/kernel/shmmax
设置其他参数
echo 65536 > /proc/sys/fs/file-max
ulimit -n 65536
echo 1024 65000 > /proc/sys/net/ipv4/ip_local_port_range
ulimit -u 16384

2、
编辑/etc/sysctl.conf
kernel.shmmax = 2147483648
kernel.shmmni = 128
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

编辑 /etc/security/limits.conf 加入一下几行
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384

重启机器
reboot
四、检查并安装相关补丁
在这个版本的RHEL上安装Oracle,必须要有几个软件包. 确认以下
rpm包都已经安装:
# rpm -qa | grep compat
compat-gcc-7.3-2.96.122
compat-libstdc++-devel-7.3-2.96.122
compat-gcc-c++-7.3-2.96.122
compat-libstdc++-7.3-2.96.122
compat-glibc-7.x-2.2.4.32.5
compat-db-4.0.14-5
# rpm -qa | grep openmotif
openmotif-2.2.2-16
openmotif-devel-2.2.2-16
#openmotif-2.2.2-16 即可
# rpm -qa | grep setarch
setarch-1.3-1
.如果个别包没有安装,把系统安装光盘mount上,找到具体的软件包(大多数在第三张光盘上),然后利用如下的命令来安装相应
的包: # rpm -ivh compat.....rpm
五、将gcc、g++更换为2.96的版本.
mv /usr/bin/gcc /usr/bin/gcc323
# ln -s /usr/bin/gcc296 /usr/bin/gcc
# mv /usr/bin/g++ /usr/bin/g++323
# ln -s /usr/bin/g++296 /usr/bin/g++
六、安装JDK
将j2sdk-1_3_1_09-linux-i586.rpm.bin拷贝到一临时目录,然后解压安装

chmod u+x j2sdk-1_3_1_09-linux-i586.rpm.bin
./j2sdk-1_3_1_09-linux-i586.rpm.bin
rpm -ivh jdk-1.3.1_09.i586.rpm

七、创建组及用户
groupadd oinstall
groupadd dba
groupadd oper
groupadd apache

useradd -g oinstall -G dba oracle
passwd oracle

useradd -g oinstall -G apache apache
passwd apache

八、安装前的准备工作

创建ORACLE的安装目录
mkdir /opt/oracle
mkdir /opt/oracle/product
mkdir -p /opt/oracle/product/9.2.0.4
chown -R oracle.oinstall /opt

以oracle用户登录
编辑 .bash_profile file:

ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/9.2.0.4; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=$ORACLE_HOME/bin:$PATH; export PATH
ORACLE_OWNER=oracle; export ORACLE_OWNER
ORACLE_SID=TSH1; export ORACLE_SID
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib; export CLASSPATH
LD_ASSUME_KERNEL=2.4.1; export LD_ASSUME_KERNEL
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

保存该配置文件,执行一下命令使其生效
Source .bash.profile

安装 3006854 patch:
unzip p3006854_9204_LINUX.zip
cd 3006854
sh rhel3_pre_install.sh

九、开始安装ORACLE
进入到ORACLE的解压目录Disk1执行一下命令,开始安装ORACLE
./runInstaller
载安装过程中,选择 \"Software Only\" 选项。
安装完成以后,进入到92040/Disk1目录运行
./runInstaller
首先安装\"Oracle Universal Installer 2.2.0.18.0\"
再安装 \"Oracle9iR2 Patch Set 3 9.2.0.4.0\"
安装就完成了
十、安装以后的工作
当实例创建完以后:
编辑 /etc/oratab 文件 ,将重启标志设为 \'Y\':
BDQJDC:/u01/app/oracle/product/9.2.0.1.0:Y
创建文件 /etc/init.d/dbora ,输入以下内容
#!/bin/sh
# chkconfig: - 20 80
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.
ORA_HOME=/opt/oracle/product/9.2.0.4
ORA_OWNER=oracle
if [! -f $ORA_HOME/bin/dbstart]
then
    echo \"Oracle startup: cannot start\"
    exit
fi
case \"$1\" in
    \'start\')
         # Start the Oracle databases:
         # The following command assumes that the oracle login
         # will not prompt the user for any values
    su - $ORA_OWNER -c $ORA_HOME/bin/dbstart &
    ;;
    \'stop\')
         # Stop the Oracle databases:
         # The following command assumes that the oracle login
         # will not prompt the user for any values
    su - $ORA_OWNER -c $ORA_HOME/bin/dbshut &
    ;;
esac
保存该文件

chmod 750 /etc/init.d/dbora
ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora
ln -s /etc/init.d/dbora /etc/rc3.d/S99dbora
加上root用户到dba 和 oinstall 组 (/etc/group) ,现在系统就可以启动和关闭数据库

还有另外一种方法使数据库能跟在系统重启时启动
1、编辑/etc/oratab文件,将对应的数据库实例最后的字段改为Y:
ORCL:/opt/oracle/product/9.2.0.4:Y
2、编辑/etc/rc.local:
添加下列内容:
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0.4
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
echo \"Starting ORACLE 9.2.0.4:\"
su - oracle -c \"$ORACLE_HOME/bin/dbstart\"
su - oracle -c \"$ORACLE_HOME/bin/lsnrctl start\"
sleep 10
3、编辑/etc/rc.d/init.d/halt:
添加下列内容:
export ORACLE_BASE=/opt/oracleexport ORACLE_HOME=$ORACLE_BASE/product/9.2.0.4
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
echo \"Shutdowning ORACLE 9.2.0.4:\"
su - oracle -c \"$ORACLE_HOME/bin/lsnrctl stop\"
su - oracle -c \"$ORACLE_HOME/bin/dbshut\"
sleep 10', 'testeset', '', 'publish', 'open', 'closed', '', 'testeset', '', '2005-05-27 13:22:58', '2005-05-27 05:22:58', '0', '0');
[27 May 2005 8:38] liuake liuake
My mysql configure file.

Attachment: my.cnf (application/octet-stream, text), 4.82 KiB.

[27 May 2005 8:39] liuake liuake
My my.cnf file is add it as a file also.
[27 May 2005 8:40] liuake liuake
the insert sql. which course trouble. pay attention to the  '–' in it.

Attachment: gb2312_insert.sql (application/octet-stream, text), 6.73 KiB.

[27 May 2005 8:44] liuake liuake
the table i use in this problem.

Attachment: create_table.sql (application/octet-stream, text), 1.26 KiB.

[27 May 2005 8:46] liuake liuake
the configuration file, the create table sql, the insert sql is attened to the files.
[27 May 2005 8:48] liuake liuake
i think is a MyISAM Table. i'm so sorry for my poor knowledge of these tables
[28 May 2005 2:42] liuake liuake
I try it use charset gbk, and it's work well.
So, i suggest that it's maybe something wrong in mysqld's gb2312 modules.
[28 Jun 2005 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[15 Jul 2005 5:32] Aleksey Kishkin
reopened, as wrongly closed to 'no feedback', despite the fact user had provided feedback
[15 Jul 2005 7:46] Aleksey Kishkin
testcase (script for query browser)

Attachment: gbtest.sql (text/x-sql), 199 bytes.

[15 Jul 2005 7:47] Aleksey Kishkin
and attached more simpe test case
[15 Jul 2005 11:17] liuake liuake
any chinese string with a character '–' will be truncated in `insert`/`update` SQL query.

see the attached files.

i test it in other computer, the same result i got.

where i changed the collection charset to GBK, it's work proper.
[17 Aug 2005 6:19] Antony Curtis
I cannot find "–" \u2013 on any GB2312 encoding table.
And 0xA843 does not appear to be a valid GB2312 character.
[18 Aug 2005 8:06] liuake liuake
You are right. 
I just find out that `–`(0xA843)  is a GBK encode character, so it's the root course. if it's decoded in GB2312,it's means changed.
thanks very much!
[20 Mar 2010 8:46] du wei
show variables like '%Max_connection%'
[20 Mar 2010 8:46] du wei
show variables like '%Max_connection%';