Openoffice连接mysql数据库的具体方法

作用就相当与ms office里面的access,不过功能应该更强大一些,因为可以连接外部数据源直接进行操作。其他的form、query、report之类的功能应该大体类似。

设置其实不难(难的是连接上以后如何利用好功能),但对于不熟悉java的人(包括我)来说就未必了,所以记录在这里分享。系统环境:Ubuntu 7.10 Gutsy, Openoffice.org 2.3.0, Mysql 5.0.45-1ubuntu3。

首先要在openoffice.org中启用java,菜单Tools->Options,Openoffice.org组的最后一项,ooo应该自动监测出了系统中已有的jre环境,如图:

Free Image Hosting at www.ImageShack.us

选中合适的jre环境,比如我选择的1.6.0_03,然后点旁边的Class Path按钮,设置类路径,由于要使用到JDBC Driver for MySQL (Connector/J),所以下载解压之后,在这里把jar文件路径设置进来,ooo在需要的时候会自动调用,如图:

Free Image Hosting at www.ImageShack.us

然后就可以新建数据库文件了,File->New->Database,注意其实ooo创建的只是一个数据库的“控制”文档。打开数据库创建向导之后,首选选择JDBC连接,然后继续:

Free Image Hosting at www.ImageShack.us

然后输入数据源地址和java类名称,针对使用JDBC Driver for MySQL (Connector/J)连接mysql数据库的情况,数据源地址格式为:

jdbc:mysql://[host][,failoverhost...][:port]/[database] [?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

所以我连本机mysql库就是:jdbc: mysql://localhost/db_name,端口默认为3306。JDBC driver class要填com.mysql.jdbc.Driver,可以用Test Class测试一下类是否加载正常。

Free Image Hosting at www.ImageShack.us

再下一步就是设置用户名以及密码了,可以点Test Connection测试是否能够正常连接,如果不正常可以检查一下mysql的监听地址、端口以及用户名/密码是否正确。

Free Image Hosting at www.ImageShack.us

最终确认,保存,就可以在ooo中看到mysql库中所有的table,以及进行下一步操作了。

Free Image Hosting at www.ImageShack.us

如果要更改数据库连接信息,使用Edit->Database菜单就可以了。

参考

在Ubuntu上安装sybase ASE 15.0.2

开始之前

先把ubuntu装上,用7.04的server安装盘默认安装即可,先装i386的,如果sybase安装顺利,再试试64位的sybase是否好用。

从sybase网站上注册免费用户之后,可以下载免费的ASE Express Edition下载页面),目前的版本是15.0.2,限制只能使用1个cpu,2G内存和管理5G数据,更详细的资料在快速安装手册中。

如果您也想自己装一个试试,建议您先跳过下面这一段。

安装

下载后的ase1502_xe_linux.tgz,直接解压到一个目录,比如ase1502中,然后运行./setup就可以开始安装了。默认的安装是java图形界面的,而我是远程ssh上去装的,所以加上-console参数运行setup。首先是欢迎信息:

Welcome to the InstallShield Wizard for Sybase Adaptive Server Enterprise Suite
version 15.0.2 GA

The InstallShield Wizard will install Sybase Adaptive Server Enterprise Suite
on your computer. Shut down all Sybase products, then click Next to begin
installation.

Press 1 for Next, 3 to Cancel or 4 to Redisplay [1] 

按1继续,接着是选择用户协议类型:

What would you like to install?

Product License

   1. Adaptive Server Enterprise for evaluation
   2. Adaptive Server Enterprise (Developer Edition)
   3. Adaptive Server Enterprise (Express Edition)

Enter one of the options above : [1] 3

1是体验版,应该是有时间限制,2是开发者版,有连接数限制,所以选3继续:

 1)  All regions 
Please enter the number of the location you are installing. (1-1) [1] 

选择自己所在的地区,只有1可选,应该没什么用处。但如果你仔细看了用户协议,会看到被美国实行贸易禁运的国家比如伊朗、伊拉克等等是禁止下载使用的。选择后会出现完整的用户协议,可以按回车一屏一屏的读完,或者用q退出,然后记得按y同意此用户协议,

Please enter the directory where you would like to install the products or
press ENTER to accept the default.

   Destination Directory [/opt/sybase]        

选择安装目录,我觉得用默认的就可以,如果目录不存在,还得确认创建新目录;如果目录已经存在,也会让你确认,不过是确认删除其中的所有文件:

      1. Yes
      2. No

   The directory does not exist.  Do you want to create it? [2] 1

然后就是选择安装哪些内容了:

Choose the setup type that best suits your needs.

[X] 1 - Typical
        The program will be installed with the suggested configuration.
        Recommended for most users.

[ ] 2 - Full
        The program will be installed with all the products and features.

[ ] 3 - Custom
        The program will be installed with the features you choose.
        Recommended for advanced users.

To select an item enter its number, or 0 when you are finished: [0] 

如果没有特殊要求,Typical就够用了,如果想自行选择安装内容,可以选3 – Custom:

Select the features for "Sybase Adaptive Server Enterprise Suite" you would
like to install:

   Sybase Adaptive Server Enterprise Suite

   To select/deselect a feature or to view its children, type its number:

     1. +[x] Sybase Servers
     2. +[x] Connectivity
     3. +[x] ASE Data Providers
     4. +[x] Language Modules
     5. +[x] jConnect for JDBC
     6.  [ ] Shared
     7. +[x] Sybase Software Asset Management
     8. +[x] Sybase Unified Agent
     9. +[x] ASE Administration Tools

   Other options:

     0. Continue installing

   Enter command [0] 

默认就是除了6 Shared,其他的都安装。最后,安装程序会把所有选项列出来供检查:

Sybase Adaptive Server Enterprise Suite will be installed in the following
location:

/opt/sybase

with the following features:

Sybase Servers
   Adaptive Server Enterprise
      ASE Agent Plugin
   Replicator
Connectivity
   Open Client
      Common ctlib files
      Common dblib files
   Common Connectivity Components
ASE Data Providers
   ASE ODBC Driver
      Unicode/MultiByte Conversion Support
      Sample Programs
Language Modules
   English Language Module
jConnect for JDBC
   JDBC Driver
Sybase Software Asset Management
   SySAM License Utilities
Sybase Unified Agent
   Unified Agent Server
   Agent Management Console
ASE Administration Tools
   Interactive SQL
   Sybase Central
      Adaptive Server Plugin
      SySAM Plugin

for a total size:

 410.8 MB

Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 

然后终于开始安装了,进度条作得蛮可爱的:

Installing Sybase Adaptive Server Enterprise Suite. Please wait...


|-----------|-----------|-----------|------------|
0%         25%         50%         75%        100%
||||||||||||||||||||||||||||||||||||||||||||||||||

Creating uninstaller...

-------------------------------------------------------------------------------
The InstallShield Wizard has successfully installed Sybase Adaptive Server
Enterprise Suite. Choose Next to continue the wizard.

Press 1 for Next, 3 to Cancel or 4 to Redisplay [1] 

接下来是配置向导,首先是email alert,用于接收数字资产变化的更新通知,显然对个人用户来讲没什么用处:

Please configure the Sybase Software Asset Management (SySAM) email alert
mechanism. When configured, specified recipients will receive email
notifications about SySAM events that may need administrator attention.

Do you want to configure email alerts?

   1. Yes
   2. No

Enter one of the options above : [1] 
SMTP server host name [smtp] smtp.gmail.com
SMTP server port number [25] 465
Sender email [root] sybase@svr6
Recipient emails [root] svr6@gmail.com
Message Severity for email alerts(信息发送级别,达到级别的才会发送邮件,1为最低/敏感的级别,3为最高/不敏感的级别)

   1. INFORMATIONAL
   2. WARNING
   3. ERROR

Enter one of the options above : [2] 

确认后就进入创建服务器的向导了,天知道上面的邮件设置能否正常发信:

The Adaptive Server Enterprise suite contains a number of programs in addition
to the Adaptive Server Enterprise database server. At this point in the
installation you can choose to configure various products. If you select the
options on this screen, you will be given the option of specifying
configuration options for each product or to allow the installer to set default
values for these on the next screen. If you do not choose to configure the
products at this time, you can configure them using the ASE configuration
utility at a later time.

Please deselect any products that you do not want to configure now :

   [X]  1  -  Configure new Adaptive Server
   [X]  2  -  Configure new Backup Server
   [X]  3  -  Configure new Monitor Server
   [X]  4  -  Configure new XP Server
   [X]  5  -  Configure Unified Agent

      To select an item enter its number, or 0 when you are finished: [0] 

直接确认,安装全部服务器:

You have chosen to configure the products listed below. You can accept default
configuration values or provide customized values for the configuration options
required by each product. If you do not select custom configuration below, this
installer will configure the products you have selected with default values. If
you accept default values at this time you can change the configuration values
for a product at a later time.

Please select the products that you want to custom configure.

   [ ]  1  -  Custom configure new Adaptive Server
   [ ]  2  -  Custom configure new Backup Server
   [ ]  3  -  Custom configure new Monitor Server
   [ ]  4  -  Custom configure new XP Server
   [ ]  5  -  Custom configure Unified Agent

      To select an item enter its number, or 0 when you are finished: [0] 

看上去和刚才的选择类似,不过这里是选择哪些服务器要用自定义设置,否则就采用默认设置快速创建了。我全部采用自定义设置([]里是默认设置,什么不输入就使用这个值了):

Please enter custom configuration values for the new Adaptive Server

   Adaptive Server Name [SVR6] 
   Port Number [5000] 
   Error Log [/opt/sybase/ASE-15_0/install/SVR6.log] /home/sybase/svr6.log
   Page Size

      1. 2k
      2. 4k
      3. 8k
      4. 16k

   Select a page size :  [1] 
   Master Device [/opt/sybase/data/master.dat] /home/sybase/master.dat
   Master Device Size (MB) [30] 60
   Master Database Size (MB) [13] 30
   System Procedure Device [/opt/sybase/data/sysprocs.dat] /home/sybase/sysprocs.dat
   System Procedure Device Size (MB) [132] 150  (系统存储过程库比以前的11.9.2版本大了很多)
   System Procedure Database Size (MB) [132] 150
   System Device [/opt/sybase/data/sybsysdb.dat] /home/sybase/sybsysdb.dat
   System Device Size (MB) [1] 20
   System Database Size (MB) [1] 10

   Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 

然后是backup server的设置:

Please enter custom configuration values for the new Backup Server

   Backup Server Name [SVR6_BS]  
   Port Number [5001] 
   Error Log [/opt/sybase/ASE-15_0/install/SVR6_BS.log] /home/sybase/svr6_bs.log

   Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 

Monitor Server:

Please enter custom configuration values for the new Monitor Server

   Monitor Server Name [SVR6_MS] 
   Port Number [5002] 
   Error Log [/opt/sybase/ASE-15_0/install/SVR6_MS.log] /home/sybase/svr6_ms.log

   Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 

XP Server:

Please enter custom configuration values for the new XP Server

   Port Number [5003] 
   Error Log [/opt/sybase/ASE-15_0/install/SVR6_XP.log] /home/sybase/svr6_xp.log

   Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 

然后是选择发现sybase服务的方法(这样客户端就不需要interface文件了),JINI需要架设单独的服务器,个人测试用UDP方式就够了,不过只能发现同一子网中的服务:

Discovery Services provide client applications the ability to find Sybase
resources within an enterprise environment without the need for local interface
file definitions. UA currently supports two discovery methods: JINI and UDP.

JINI requires a dedicated JINI server to provide discovery services and can
provide these services throughout the enterprise. UDP does not require a
dedicated server but is limited to discovery within the local subnet.

   Adaptor

      1. JINI
      2. UDP

   Select the adaptor for the Self Discovery Service. [2] 

然后是选择用户统一认证方式,我觉得没有特殊理由,就用默认的好了,和11.9.2的使用习惯应该是相同的:

Please select and order the security login modules for the Unified Agent.

Simple Login: authenticates user ID/password against entries within a UA
configuration file.

ASE Login Module: delegates authenticationto an appropriate role (e.g.,
sa_role, sso_role, etc.) within the ASE.

NT/Unix Proxy login Module: delegates authentication to a user group in the
underlying operating system (e.g., Administrators, root, etc.).


      1.  Simple Login Module      true
      2.  ASE Login Module         true
      3.  Unix Proxy Login Module  false

   Select a login module to modify.  Enter "0" when you are done: [0] 

最后,依然是把所有设置print出来检查,其中Unified Agent部分的有些内容应该以后会用到:

   Unified Agent - Security Login Modules

      CSI.loginModule.1.provider             com.sybase.ua.services.security.simple.SimpleLoginModule
      CSI.loginModule.1.controlFlag          sufficient
      CSI.loginModule.1.options.moduleName   Simple Login Module
      CSI.loginModule.1.options.username     uafadmin
      CSI.loginModule.1.options.password     ******
      CSI.loginModule.1.options.roles        uaAgentAdmin,uaPluginAdmin
      CSI.loginModule.1.options.encrypted    false

      CSI.loginModule.2.provider             com.sybase.ua.services.security.ase.ASELoginModule
      CSI.loginModule.2.controlFlag          sufficient
      CSI.loginModule.2.options.moduleName   ASE Login Module

终于又开始创建服务器了,不幸又遇到了错误:

Building Adaptive Server 'SVR6':
Writing entry into directory services...
Directory services entry complete.
Building master device...
Task failed
Server 'SVR6' was not created.
/opt/sybase/ASE-15_0/bin/dataserver: error while loading shared libraries:
libstdc++.so.5: cannot open shared object file: No such file or directory

其它服务器创建倒是很正常,除了连不上主服务器:

Building Backup Server 'SVR6_BS':
Writing entry into directory services...
Directory services entry complete.
Writing RUN_SVR6_BS file...
RUN_SVR6_BS file complete.
Starting server...
Server started.
Server 'SVR6_BS' was successfully created.

Building Monitor Server 'SVR6_MS':
Writing entry into directory services...
Directory services entry complete.
Writing RUN_SVR6_MS file...
RUN_SVR6_MS file complete.
Unable to log in to Adaptive Server 'SVR6'.
Unable to boot 'SVR6_MS' because Monitor Server must be able to log in to 
Adaptive Server.
Server 'SVR6_MS' was not created.

Building XP Server 'SVR6_XP':
Writing entry into directory services...
Directory services entry complete.
Server 'SVR6_XP' was successfully created.
'SVR6_XP' will be started when needed by the Adaptive Server.

主服务器可能是缺少库文件,安装上libstdc++5包(会自动安装gcc-3.3-base包),用sybase用户进入,设定好环境变量,在.bashrc里添加上两行:

. ~/SYBASE.sh
. ~/ASE150.sh

再用rs文件创建服务器(记得先关掉已经启动的backup服务器,删掉/opt/sybase目录)。但,依然是错误:

   Building Adaptive Server 'SVR6':
   Writing entry into directory services...
   Directory services entry complete.
   Building master device...
   Task failed
   dataserver: master device size for this server: 60.0 Mb
   Server 'SVR6' was not created.
   dataserver: master database size for this server: 13.0 Mb
   dataserver: model database size for this server: 3.0 Mb

log的最后部分是:

   00:00000:00000:2007/09/17 02:31:14.28 kernel Encryption provider
   initialization succeeded on engine 0.
   00:00000:00000:2007/09/17 02:31:14.28 kernel  engine 0, os pid 28397  online
   Segmentation fault

上网查资料,又加上了LD_POINTER_GUARD=0参数(这个是0或者1得来回试试,看哪个行),终于行了:

Building Adaptive Server 'SVR6':
Writing entry into directory services...
Directory services entry complete.
Building master device...
Master device complete.
Writing RUN_SVR6 file...
RUN_SVR6 file complete.
Starting server...
Server started.
Building sysprocs device and sybsystemprocs database...
sysprocs device and sybsystemprocs database created.
Running installmaster script to install system stored procedures...
installmaster: 10% complete.
installmaster: 20% complete.
installmaster: 30% complete.
installmaster: 40% complete.
installmaster: 50% complete.
installmaster: 60% complete.
installmaster: 70% complete.
installmaster: 80% complete.
installmaster: 90% complete.
installmaster: 100% complete.
installmaster script complete.
Creating two-phase commit database...
Two phase commit database complete.
Installing common character sets (Code Page 437, Code Page 850, ISO Latin-1, 
Macintosh and HP Roman-8)...
Character sets installed.
Setting server name in Adaptive Server...
Server name added.
Server 'SVR6' was successfully created.

安装小结

Sybase对服务器环境比较挑剔,我的服务器操作系统是Ubuntu 7.04:

  • 首先libaio是需要的,apt安装即可,表现为libaio-devlibaio1两个包。
  • 设置shmmax,在/etc/sysctl.conf中加入kernel.shmmax=2147483648(2G共享内存),重启,或者直接echo 2147483648 > /proc/sys/kernel/shmmax,或者用sysctl -p直接加载。查询目前的共享内存设置用sysctl kernel.shmmax,单位为字节,sybase应该是需要至少64M,建议根据机器配置情况,设置为总内存的一半,或者256M以上。
  • 安装上libstdc++5包,会自动安装gcc-3.3-base包。
  • 手工创建sybase用户,把/home/sybase的权限设给它,并设置$HOME/opt/sybase,当然,这一步也可以在安装完sybase、创建服务器之前作。
  • 在sybase用户的bashrc中添加LD_POINTER_GUARD=0(注:我把sybase的默认shell从sh改成bash了,好像RHEL中需要指定这个环境变量为1),在bashrc增加两个sh脚本的调用(. ~/SYBASE.sh. ~/ASE150.sh)。
  • 文档中建议使用sybase用户来进行安装,不过我是用root用户安装的,好像也没有遇到太多问题,安装完成后把/opt/sybase整体chown一下。

服务器设置

更改sa密码

格式为sp_password oldpassword,newpassword,比如:

>sp_password null,"new_password"
>go

服务器的字符集

默认字符集是“iso_1 (ISO 8859-1), a binary sort order, and English as the default language.”,我们一般使用的字符集是cp936或者utf8,下面就把服务器字符集调整为utf8。

没有用sp_configure来配置服务器字符集,而是采用sqlloc.rs方式,rs文件内容如下:

sybinit.release_directory: /opt/sybase
sqlsrv.server_name: SVR6
sqlsrv.sa_login: sa
sqlsrv.sa_password:
sqlsrv.default_language: us_english
sqlsrv.language_install_list: USE_DEFAULT
sqlsrv.language_remove_list: USE_DEFAULT
sqlsrv.default_characterset: utf8
sqlsrv.characterset_install_list: USE_DEFAULT
sqlsrv.characterset_remove_list: USE_DEFAULT
sqlsrv.sort_order: binary

运行之:

sybase@svr6:~$ sqllocres -r sqlloc.rs 
Installing sort order 'Binary ordering, for the ISO 10646-1, UTF-8 multibyte 
encodin...' in the Adaptive Server...
Sort Order 'Binary ordering, for the ISO 10646-1, UTF-8 multibyte encodin...' 
was successfully installed in the Adaptive Server.
Making 'UTF-8' the Adaptive Server's default character set...
Making 'Binary ordering, for the ISO 10646-1, UTF-8 multibyte encodin...' the 
Adaptive Server's default sort order...
The Adaptive Server's default character set is now 'UTF-8'.
The Adaptive Server's default sort order is now 'Binary ordering, for the ISO 
10646-1, UTF-8 multibyte encodin...'.
Done

在isql中检查转换结果:

1> sp_helpsort
2> go
...
 Character Set = 190, utf8
     Unicode 3.1 UTF-8 Character Set
     Class 2 Character Set
 Sort Order = 50, bin_utf8
     Binary sort order for the ISO 10646-1, UTF-8 multibyte encodin
     g character set (utf8).
(return status = 0)
1> select @@client_csname
2> go

 ------------------------------
 utf8

(1 row affected)

简单试了一下,客户端是cp936字符集的时候,服务器能够正常的转换编码。

还有一个问题需要在数据转移时考虑,原来的服务器字符集是cp936,在转移数据的时候应该会遇到字段长度不够的情况,需要把字段长度增加为现在的3/2倍。

Adaptive Server Enterprise 15.0 – Datatypes

客户端的字符集设置需要修改$SYBASE/locales/locales.dat,在指定的操作系统如[win32]段,添加:

locale = default, us_english, cp936

就可以了,在数据传输过程中sybase会自动进行cp936和utf8之间的转换。一点小问题:我在linux下用sqsh连接服务器,不管-J参数怎么设置,select @@client_csname都是NULL,不过中文好像也能够正常使用(linux为Ubuntu,字符集为en_US.utf8)

其它关于字符集的参考资料:

自定义cfg文件

内存
max memory = 1048576

2k的页大小,设置2G的内存,XE版中设置超过这个值的话,反而会被ASE拒绝,使用默认值49152。

cpu
max online engines = 1

其实这一项使用DEFAULT即可,因为XE的限制,设置多了也不会起作用。

其他参数
number of user connections = 300
number of locks = 10000 (ASE 15默认值就是这个)
lock scheme = datarows
identity burning set factor

这个是用来自动分配identity列的值的,sybase采用预分配区间的方式,所以重启或rollback事务会产生断带,这个参数就是用来控制断带的大小的,公式为:

identity burning set factor(F) = N * power(10, 7 - M)

其中N是每次分配的种子数量,M为identity列的长度。反过来,想知道每次会分配多少种子数,公式就是:

N = F * power(10, M - 7)

在我这里,identity列长度为8/numeric(8,0),identity burning set factor设置为10,每次的种子分配就是100个,最大的断带也就是100。

其它文档中提到,但是不需要运行的

  • installmaster
  • installcommit 两阶段提交或分布式事务(two-phase commit or distributed transactions)
  • installsecurity (Sybase auditing feature)
  • instmsgs.ebf (brings your Adaptive Server messages up to the level of this EBF)
  • installjsdb (Job Scheduler feature enabled)and(upgrade the Job Scheduler Templates)
  • installmontables (update the Monitoring Tables’ definitions)

文档中都有详细说明,用isql执行相应sql文件即可,我没有装。

创建init.d脚本

我的安装并没有自动在/etc/init.d/下创建控制服务的脚本,所以只能自己按照SQL Relay上的例子创建一个了(有一点点改动):

#!/bin/sh

export SYBASE=/opt/sybase
export PATH=$PATH:$SYBASE/ASE-15_0/bin:$SYBASE/ASE-15_0/install
export LD_POINTER_GUARD=0
#echo 268435456 > /proc/sys/kernel/shmmax
case "$1" in
        start)  
                for i in `ls $SYBASE/ASE-15_0/install/RUN_*`
                do
                        su -c "$SYBASE/ASE-15_0/install/startserver -f $i" > /dev/null 2>&1;
                        #su -c "$SYBASE/ASE-15_0/install/startserver -f $i"
                done
                ;;
        stop)   
                kill `ps -efa | grep sybase | grep -v grep | awk '{print $2}'`
                ;;
        *)
                echo $"Usage: $0 {start|stop}"
                exit 1
esac

exit 0

这里还有一个更好的脚本,主要是关闭使用的不是kill,而是数据库自身的shutdown功能),针对我的平台,我有略微改动,建议大家使用这个或参照修改:

#!/bin/bash
# Author: MichaelBibby
# Date: 2007.06.19
# description: Normal RC script for SYBASE.

SYBASE_USERNAME="sa"
SYBASE_PASSWORD=""

export LD_POINTER_GUARD=0
export SYBASE=/opt/sybase
export SYBASE_HOME='/opt/sybase'
export SYBASE_OCS='OCS-15_0'
export SYBASE_ASE='ASE-15_0'
# ************************************************
# Warnning: Modify the RUN_xxx to fit your need.
# Your startup script will be such as:
#   ${SYBASE_HOME}/${SYBASE_ASE}/install/RUN_${SERVER}
#   ${SYBASE_HOME}/${SYBASE_ASE}/install/RUN_${SERVER_BACKUP}
export SERVER='SVR6'
export SERVER_BACKUP='SVR6_BS'
# For multi-server
# export SERVER='server1 server2'
# export SERVER_BACKUP='server1_backup server2_backup'
# ************************************************

export LIB="${SYBASE_HOME}/${SYBASE_OCS}/lib"
export PATH=$PATH:"${SYBASE_HOME}/${SYBASE_ASE}/bin:${SYBASE_HOME}/${SYBASE_ASE}/install:${SYBASE_HOME}/${SYBASE_OCS}/bin"

start_db()
{
    su -p sybase -c "${SYBASE_HOME}/${SYBASE_ASE}/install/startserver \
    -f ${SYBASE_HOME}/${SYBASE_ASE}/install/RUN_${SERVER}"

    su -p sybase -c "${SYBASE_HOME}/${SYBASE_ASE}/install/startserver \
    -f ${SYBASE_HOME}/${SYBASE_ASE}/install/RUN_${SERVER_BACKUP}"
}

stop_db()
{
# Error: Site 'SVR6_BS' not found in sysservers/ User default back_srv name.
    ${SYBASE_HOME}/${SYBASE_OCS}/bin/isql \
    -U${SYBASE_USERNAME} \
    -P${SYBASE_PASSWORD} \
    -S${SERVER} <<EOF
shutdown SYB_BACKUP
go
exitEOF

    ${SYBASE_HOME}/${SYBASE_OCS}/bin/isql \
    -U${SYBASE_USERNAME} \
    -P${SYBASE_PASSWORD} \
    -S${SERVER} <<EOF
use master
go  
checkpoint
go
shutdown  
go
exit
EOF

if [ X"$?" == X"0" ]; then
    exit 0
else
    exit 1
fi
}

status_db()
{
    chk_sybase=$(ps axw --cols 32768 | grep dataserver | grep -v grep | wc -l)
    if [ X"$chk_sybase" = X"0" ]
    then
        echo -n "Checking Master Server: ${SERVER}"
        echo " ... Failed."
        echo
    else
        echo -n "Checking Master Server: ${SERVER_BACKUP}" && echo " ... Success."
    fi

    chk_sybase_bs=$(ps axw --cols 32768 | grep backupserver | grep -v grep | wc -l) 
    if [ X"$chk_sybase_bs" = X"0" ];then
        echo -n "Checking Backup Server: ${SERVER_BACKUP}"
        echo " ... Failed."
        echo
    else
        echo -n "Checking Backup Server: ${SERVER_BACKUP}" && echo " ... Success."
    fi

    exit 0
}   


case $1 in
    restart) stop_db ; start_db ;;
    start) start_db ;;    stop)
        ps aux | grep -i dataserver | grep -v grep >/dev/null
        if [ X"$?" == X"0" ]; then
            stop_db
        else
            exit 0
        fi
        ;;
    status) status_db ;;
    *) echo "USAGE: $0 [start|stop|status|restart]" ;;
esac

把这个脚本ln为/etc/init.d/sybase就可以用来控制服务了。

开机后自动启动

把刚才创建的/etc/init.d/sybase这个文件ln到各个rc目录下就可以了:

cd /etc/rc0.d/
ln -s ../init.d/sybase K90sybase
cd /etc/rc1.d/
ln -s ../init.d/sybase K90sybase
cd /etc/rc2.d/
ln -s ../init.d/sybase S90sybase
cd /etc/rc3.d/
ln -s ../init.d/sybase K90sybase
cd /etc/rc4.d/
ln -s ../init.d/sybase K90sybase
cd /etc/rc5.d/
ln -s ../init.d/sybase K90sybase
cd /etc/rc6.d/
ln -s ../init.d/sybase K90sybase
cd /etc/rcS.d/
ln -s ../init.d/sybase S90sybase

绑定两个ip地址

默认情况下,sybase只在一个ip地址上监听,如果在interfaces文件中强行添加其他的ip,或者是其他的端口,比如这样:

SVR6
        master tcp ether svr6 5000
        query tcp ether svr6 5000
        master tcp ether svr6 4000
        query tcp ether svr6 4000
        master tcp ether 192.168.0.106 5008
        query tcp ether 192.168.0.106 5008

都用svr6这个名字,5000和4000端口都可以监听成功,但第二个106的地址监听不成功:

00:00000:00008:2007/09/18 13:43:49.84 kernel  network name svr6, interface IPv4, address 0.0.0.0, type tcp, port 5000, filter NONE
00:00000:00008:2007/09/18 13:43:49.84 kernel  network name svr6, interface IPv4, address 0.0.0.0, type tcp, port 4000, filter NONE
00:00000:00008:2007/09/18 13:43:55.17 kernel  ninit: cannot find host 192.168.0.106
00:00000:00008:2007/09/18 13:43:55.17 kernel  Cannot allocate resources for listener with protocol tcp, host 192.168.0.106, port 5008, engine 0.

所以变通的办法无外乎两种,一种是用防火墙把另外一个ip地址的端口作dnat转发过来,另外一种就是修改hosts文件,把svr6的地址指定为0.0.0.0,个人认为第二种方式较好一些,并且试验成功。

创建数据库

设定以后的用户数据库不要默认创建在master设备上

1> sp_diskdefault master, defaultoff
2> go

更改tempdb的大小(建议使用下面ram设备的方式)

先查看tempdb的状态:sp_helpdb tempdb,默认是创建在master设备上,大小为4M,而将临时数据库放在单独的设备上更好,现在就来调整:

> disk init     # 初始化设备
> name="tempdb_dev",    # 设备名称,遵守sybase标识符规则
> physname="/opt/sybase/tempdb.dat",    #设备物理地址,最好用绝对地址
> vdevno=13,        # 设备编号(需要唯一,我刚装上的系统,master是0,sysprocsdev是1,systemdbdev是2,再新建就该用3了)
> dsync=false,      # 关闭I/O缓存,提升性能
> size=15360        # 设备大小,默认是以块为单位(一般512字节或者2k),不过建议使用尺寸,比如"200M"或者"1g",这样更容易掌握一些)
> go

将临时数据库扩展到该一个设备上,注意这里的单位是M

> alter database tempdb on tempdb_dev=30
> go

打开tempdb数据库,从段上删除master设备

> use tempdb
> go
> sp_dropsegment default, tempdb, master
> go
> sp_dropsegment logsegment, tempdb, master 
> go
> sp_dropsegment system, tempdb, master 
> go

设置tempdb为第一个recover的库(好像没必要)

-- make the second tempdb the first user db to be recovered.
> sp_dbrecovery_order tempdb_report, 1
> go

参考:disk init手册

把tempdb创建在ram设备上

首先创建虚拟的ram设备,挂载上(默认的大小单位好像是1k,但建议适当多划分一点空间留给文件系统和容纳计算误差,比如我想用30M的tempdb,就划了33000),创建设备文件,并更改文件权限:

mkdir /home/sybase/tempdb
mke2fs -q -m0 /dev/ram0 33000
mount -t ext2 /dev/ram0 /home/sybase/tempdb
?? touch /home/sybase/tempdb/tempdb.dat (第一次创建的时候不建文件,加在启动脚本里再用touch?)
chown -R sybase:sybase /home/sybase/tempdb

然后在这个设备文件上创建sybase数据库设备,并把tempdb转移过来,首先创建设备:

> disk init name="tempdb_dev", phyname="/home/sybase/tempdb/tempdb.dat", vdevno=3,dsync=false,size="30M"
> go

在新设备上分配空间:

> use master
> alter database tempdb on tempdb_dev=30
> go
Extending database by 15360 pages (30.0 megabytes) on disk tempdb_dev

从旧设备(master)上删除段:

> use tempdb
> go
> sp_dropsegment "default", tempdb, master
> go
> sp_dropsegment logsegment, tempdb, master 
> go
> sp_dropsegment system, tempdb, master 
> go

回收旧设备上的空间,还是直接修改系统表最简单,不过最好先查询一下,对照sysusages表的内容进行适应性调整:

> sp_configure "allow update", 1
> go
> begin tran
> delete from sysusages where dbid=2 and segmap=0
> go
> update sysusages set lstart=0 where dbid=2
> go
> commit
> go
> sp_configure "allow update", 0
> go

还有一种用临时表,写日志然后截断日志的方式,可以参考同一设备数据库如何分离日志与数据,不过我作的时候没有成功,还因为日志堵塞动弹不得(我循环了50万次都没事,然后全部delete from就出事了 :);另外一次是在执行sql insert into t select id from t时,第一次执行没事,5万条数据出来了,第二次再执行。。。死掉了)。可以用sp_helpdb tempdb查设备和段分配情况,

为了在重启的时候也能正常创建ram盘,还要在上面说过的服务控制脚本中添加创建并使用ram盘的内容,即在start_db的开始部分加上:

start_db()
{
    echo "Mounting ramdisk for tempdb ..."
    mke2fs -q -m0 /dev/ram0 33000
    mount -t ext2 /dev/ram0 /home/sybase/tempdb
    touch /home/sybase/tempdb/tempdb.dat
    chown -R sybase:sybase /home/sybase/tempdb
    ......

在stop_db()的结尾部分加上:

    ......      
    echo "Dismounting ramdisk of tempdb ..."
    umount /home/sybase/tempdb

    if [ X"$?" == X"0" ]; then
        exit 0
    else
        exit 1
    fi
} // end of func stop_db

两个疑惑的地方及我看到的结果:

  • mke2fs创建的ram盘占用实际的内存么?好像不占,分配到文件上才占。
  • sybase重启后,tempdb会重用大小和以前不同的同名设备文件么?会的,tempdb的机制和其他用户库不一样。

参考:

搞定收工,可以正式建库配置应用数据库了,再推荐几个sybase的资料:

索引的问题(未解决)

服务器环境:windows2000,sybase ASE 11.9.2

一个表,里面的三个字段A B C均为字符串类型(varchar或者char),其中C的实际数据为c1, c2, c3等有限的几种。表的主键和索引都不是ABC三个字段。下面的sql语句:

SELECT * FROM tbl_name WHERE A='a' AND (C='c1' OR (C='c2' AND B='b'))

是能够正常执行,得到数据的。但是当在字段C是创建索引:

CREATE INDEX idx_name ON tbl_name(C)

之后,上面的SELECT sql语句也能正常执行,但却无法得到数据了。而在创建索引前后,符合WHERE条件的数据是肯定存在的,所以可以理解为这个索引导致了sql的执行结果无数据;在删除索引之后,马上就又能取到数据了。

这是索引有问题?还是索引损坏?还是其它的什么问题?

触发器的教训

触发器是高级dbms的特性之一,可以在数据insert、delete或者update的时候,自动的进行处理,比如根据变化的数据内容对其他数据进行适应性调整。但今天我却因为这个遇到了一点小麻烦。

我的数据库是sybase 11.9.2,这个版本不支持ALTER TABLE tbl_name MODIFY col_name datatype语法,所以只能采取变通的方式,比如要把col从decimal(8,2)更改为decimal(14,2)类型:

  1. 新建colA,类型为decimal(14,2)
  2. UPDATE table SET colA=col
  3. ALTER table DROP col
  4. sp_rename “table.colA”, col

但过程中,接二连三的遇到问题,首先进行SET colA=col的时候,由于表含有一个非主键索引,所以很慢,这个只能忍,下次记得先删除索引,好在数据不多。

然后是锁类型,为了提高并发性我更改成了DATAROWS,而这种锁类型下是不允许DROP column的,所以要变更回ALLPAGES,DROP列之后再变回来。

然后是sp_rename存储过程出了莫名其妙的错误(忘记具体内容了,反正不能用),所以上面的第4步变成了再新建列col,然后SET col=colA把值再写回来,然后再删除colA。

最后,也是最致命的问题,就是这个表上还有update和delete的触发器,我太大意了,以至于在第二次UPDATE的时候才发现,但这时已经晚了,整表的UPDATE,还有其它的一些实时处理,让整个数据库系统几乎瘫痪,虽然进程不多,但由于表之间的关联和sybase的加锁机制,很多进程都成了send sleep或者sleeping状态,再过一会儿,日志满了,又多了一些等待日志空间释放的sleep进程。这个时候,drop trigger操作已经无法执行了,一方面是drop trigger和这个表的其它进程锁冲突,另一方面是当事务正在进行的过程中无法truncate日志。最后,只能中止客户端操作,并重启数据库服务。

真正的噩梦才刚刚开始,drop trigger之后,所有update操作都完成了,col类型更改的任务完成了,再重新把trigger建上,但很快发现,只要有针对这个表的update操作,即使操作的对象集为空,数据库服务器也立刻陷入一种死循环状态,update操作永远执行不完,并且进程无法kill,用户越连越多,最后不仅日志很快又满了,而且用户连接数也用完了。找了一下午原因,发现还是drop掉这个update的trigger之后就没问题了,猜测可能是由于某种客户端操作的中止,使这个表的为触发器预留的inserted和deleted数据缓存滞留在系统中,并随着trigger的触发再次被处理,进入死循环,重启服务之后,依然存在,然后再被触发,再造成死循环。

最终,新建了一个同结构的表,把数据全部导过去,然后重建索引、触发器,最后再删除旧表,把新表的名称改成现在用的这个,问题终于解决了。同时也从侧面证实了我的猜想,问题就出在这个表上。按说sybase应该不会出现这样的问题的。

总结一下,再更改字段类型的时候,应该按照这样的顺序操作:

  1. 删除触发器和索引
  2. 添加新字段colA
  3. col的值赋给colA
  4. 更改锁类型为ALLPAGES
  5. 删除字段col
  6. 更改锁类型为DATAROWS
  7. 添加新字段col
  8. colA的值再赋给col
  9. 更改锁类型为ALLPAGES
  10. 删除字段colA
  11. 更改锁类型为DATAROWS
  12. 重建索引和触发器

重复更改锁类型的原因是在ALLPAGES锁类型的情况下,进行update的操作比较慢,影响其它同时进行着的操作。并且在高版本的sybase,比如ASE 15下,就不用这么麻烦了,可以使用”ALTER TABLE tbl_name MODIFY col_name datatype”这样的SQL来直接实现。

数据库使用优化的一点心得

在生产系统中,数据库经常会成为瓶颈,给管理员带来无穷麻烦,所以dba才那么值钱啊。但对一些中小型的应用来说,也许没有好的dba来从数据库本身机制管理上来进行优化,或者现有系统特性不允许太大的改动,加上数据库不像web服务器那样可以简单的通过集群来扩容,这样就需要从应用程序的角度来进行优化了,尤其是应用程序使用数据库的方式:

  • 减少复杂的sql,多用简单的sql。
  • 在sql中减少运算的使用,除了count(1)。
  • 索引和条件中,多用int类型,少用string类型。
  • 尽量不要用触发器,无必要不要用存储过程,慎用事务。
  • 注意where条件的顺序。

从系统设计思路上来说:

  • 将大型的操作拆分为小型的操作,用比sql更灵活的web语言来实现部分内容。
  • 程序对数据库连续取数据进行操作的时候,可以考虑对已经取过的数据适当的进行缓存,减少重复执行sql的过程。
  • 常用但不常更新的页面,可以考虑静态化,减轻数据库压力。
  • 控制图片的数量和体积,善用css,启动服务器端压缩以节约带宽。

从数据库自身设计来说:

  • 配合常用查询,对索引进行优化改进。
  • 在数据库设计中适当保留冗余,针对访问速度进行优化。

最后一点教训:最好在设计初期就开始考虑性能的问题,贯穿整个开发进程,否则投入使用以后再进行修改,就要花费数倍的精力了;而设计时最不易发现问题的原因就是数据量小、访问量小,数据可以进行造假,访问量可以用ab等压力测试工具来模拟,但这些都和真实环境有相当大的偏差,不要太相信就是了。

以上只是一点拙见,抛砖引玉,欢迎拍砖。

参考

把DH主机上的mysql字符集倒腾为utf8

很久没有倒腾过mysql的字符集了,都疏忽了,以致于为LP安装Dreamhost上主机的时候,居然没有修改mysql数据库的字符集。也是由于mysql这东西,即使字符集设置错了,也是吃啥吐啥,拿错误的字符集就往数据库里存,所以外面根本看不出来。今天给LP写了个自动备份脚本,才发现导出的sql中全是乱码,岂能容忍?遂解决之~

首先还是放松了警惕,走了个弯路,我习惯使用的dump脚本是:

mysqldump --add-drop-table --default-character-set=utf8 -h mysql.mydomain.com --user=fwolf --password=mypasswd --extended-insert=false fwolf_db > mydomain.com_db.sql

可是dump出来的sql一看中文全是乱码,于是去掉--default-character-set=utf8再dump,数据依旧,一时没想过来,用iconv把dump出来的sql左转换右转换怎么也是不对,后来才想明白,DH上mysql字符集的默认设置是这样的:

mysql> show variables where variable_name like 'char%';
+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | latin1                                     |
| character_set_connection | latin1                                     |
| character_set_database   | latin1                                       |
| character_set_filesystem | binary                                     |
| character_set_results    | latin1                                     |
| character_set_server     | latin1                                     |
| character_set_system     | utf8                                       |
| character_sets_dir       | /data/mysql/ovadilla/share/mysql/charsets/ |
+--------------------------+--------------------------------------------+
8 rows in set (0.00 sec)

数据库服务器字符集是latin1,也就是mysql默认的latin1_swedish_ci,而系统的默认字符集则是utf8,所以不带--default-character-set=utf8参数dump的数据的时候,其实和带上这个参数是一样的!想通这个问题,又知道了mysql吃啥吐啥的特性之后,解决方法就简单了,记住如果WordPress和mysql都是默认设置的情况下,如果你能夠正常浏览中文,则说明配置就是错误的,因为WordPress默认也不是utf8字符集的。

首先,按latin1字符集导出sql:

mysqldump --add-drop-table --default-character-set=latin1 -h mysql.mydomain.com --user=fwolf --password=mypasswd --extended-insert=false fwolf_db > mydomain.com_db.sql

这样导出的sql中的中文应该都是正确的了,现在修改导出的sql文件,把其中的latin1全部替换为utf8,然后再重新导入数据库,导入的时候强制指定字符集为utf8:

mysql --default-character-set=utf8 fwolf_db -h mysql.mydomain.com -u fwolf -pmypasswd < mydomain.com_db.sql

这样,如果直接用mysql查询数据的话,如果不使用set names 'utf8',中文应该是乱码的,而如果你的数据库字符集仍然是默认的latin1的话,会是相反的情况:不使用set names 'utf8',中文正常,使用之后反而乱码。确认数据库字符集调整正确之后,再来搞定WordPress,这个就简单多了,编辑文件WP/wp-includes/wp-db.php,大概66行,在$this->select($dbname);前面,加上:

$this->query('set names utf8');

这样就全部搞定了,数据库和Wordpress的字符集全部调整为万国码utf8。

参考:

PS1: DH主机ssh、svn、cron、php-cli、fsocketopen什么的全部支持,太爽了,怪不得那么多人喜爱,就是ssh的速度慢了些。

PS2: 一个和mysql吃啥吐啥有关的笑话,不太雅:你来到医院,向医生说:我近来很不正常,吃什么拉什么,吃黄豆拉黄豆,吃西瓜拉西瓜,你问医生怎样才能恢复正常,医生沉默了一阵说:你吃屎吧!

Update @ 2007-07-20

mt主机上的mysql是4.1版本,查服务器配置变量的sql好像略微有些不同,不用where了:

show variables  like 'char%';