| 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: | |
| 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
[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%';
