谢太数据库: August 2011

Tuesday, August 30, 2011

Wednesday, August 17, 2011

Getting MySQL process list with GDB

用GDB调试MySQL，显示线程列表

define helper functions

cat >mysql.gdb <<'EOF'
#set interactive-mode off
set confirm off
dont-repeat
define show_processlist
  #argc is not available in older version gdb
  #if $argc==1
  #  set $p_thread_id=(long long)$arg0
  #else
    set $p_thread_id=(long long)-1
  #end
  set $current=((base_ilist)threads).first
  set $last=&(((base_ilist)threads).last)
  printf "Id\tUser\tHost\tDb\tCommand\tTime\tState\tInfo\n"
  while $current != $last
    set $tmp=(class THD *)$current
    set $tmp_sctx=$tmp->security_ctx
    if ( $p_thread_id == -1 || $p_thread_id == $tmp->thread_id )
      set $id=$tmp->thread_id
      set $user=(char*)($tmp_sctx->user?$tmp_sctx->user:($tmp->system_thread?"system user":"unauthenticated user"))
      set $host=$tmp_sctx->host_or_ip
      set $db=($tmp->db)?$tmp->db:"NULL"
      set $command=(int)($tmp->command)
      #set $proc_info=(char*)(($tmp->killed==THD::KILL_CONNECTION)?"Killed":0) #5.1
      set $proc_info=(char*)($tmp->killed?"Killed":0)
      set $state_info=(char*) ($tmp->locked ? "Locked" : $tmp->net.reading_or_writing ? ($tmp->net.reading_or_writing == 2 ? "Writing to net" : $command == COM_SLEEP ? "" : "Reading from net") : $tmp->proc_info ? $tmp->proc_info : ($tmp->mysys_var && $tmp->mysys_var->current_cond) ? "Waiting on cond" : "NULL")
      set $start_time=$tmp->start_time
      #set $now=my_time(0) #5.1
      set $now=time((time_t*) 0)
      set $query=(char*)($tmp->query_string.str?$tmp->query_string.str:"")
      printf "%u\t%s\t%s\t%s\t%s\t%u\t%s\t%.100s\n",$id,$user,$host,$db,command_name[$command].str,$now-$start_time,$state_info,$query
    end
    set $current=$current->next
  end
end
document show_processlist
  Prints MySQL process list.
  Syntax: show_processlist [thread id]
  Examples:
    show_processlist
    show_processlist 123
    show_processlist -1
end
define kill_thread
  #if $argc >= 1
    set $p_thread_id=(unsigned long long)$arg0
    #set $p_only_kill_query=(char *)(($argc >= 2)?$arg1:"connection")
    set $p_only_kill_query=(char *)$arg1
    set $only_kill_query=(strcmp($p_only_kill_query,(char *)"query")==0)?1:0
    set $current=((base_ilist)threads).first
    set $last=&(((base_ilist)threads).last)
    while $current != $last
      set $tmp=(class THD *)$current
      if $p_thread_id == $tmp->thread_id
        set $id=$tmp->thread_id
        printf "kill thread id %d %s\n", $id, $only_kill_query?"only query":""
        #call kill_one_thread($tmp, $id, $only_kill_query)
        call sql_kill($tmp, $id, $only_kill_query)
        loop_break
      end
      set $current=$current->next
    end
  #else
  #  help kill_thread
  #end
end
document kill_thread
  Kills MySQL thread.
  Syntax: kill_thread <thread id> <query|connection>
  Examples:
    kill_thread 123
    kill_thread 123 "connection"
    kill_thread 321 "query"
end
EOF

show processlist

mysql_port=8217
# older version gdb does not support '-ex' option
#gdb -batch -silent -p $(pgrep -f "/mysqld .*$mysql_port"|head -n1) -x mysql.gdb -ex 'show_processlist'
gdb -silent -p $(pgrep -f "/mysqld .*$mysql_port"|head -n1) -x mysql.gdb <<'EOF' |\
sed -n '/^=====begin=====$/,/=====end=====/p'|sed '1d;$d'
set prompt
set pagination off
printf "\n\n"
printf "=====begin=====\n"
show_processlist
printf "=====end=====\n"
EOF

kill thread

thread_id=223
gdb -silent -p $(pgrep -f "/mysqld .*$mysql_port"|head -n1) -x mysql.gdb <<EOF
kill_thread $thread_id "connect"
EOF

Tuesday, August 16, 2011

MySQL General Query Log

==参考文档==
The General Query Log: http://dev.mysql.com/doc/refman/5.1/en/query-log.html
Selecting General Query and Slow Query Log Output Destinations: http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html
mysql-5.1.52.tar.gz mysql-5.1.52.tar.gz

==系统变量==
MySQL 5.1.29+
* general_log 普通查询日志开关
* general_log_file 普通查询日志文件名
* log_output 日志输出方式
* sql_log_off 会话级普通查询日志开关

==代码分析==

* 日志相关操作的代码文件是：sql/log.cc和sql/log.h

* 记录日志函数只有两个：general_log_write和general_log_print
** 两者区别主要是输入的参数不一样，general_log_print是可变参数的，general_log_write参数数量是固定的

* 函数内判断操作是否需要记录日志。判断依据是：

  if (*general_log_handler_list && (what_to_log & (1L << (uint) command)))

what_to_log变量在sql/mysqld.cc初始化为二进制的15个1（COM_TIME等于15）

what_to_log= ~ (1L << (uint) COM_TIME);

因此判断条件(what_to_log & (1L << (uint) command))总是真的，默认所有操作都被记录。好像没有手工设置what_to_log的地方？

* MySQL其它程序调general_log_write或general_log_print函数写日志，比如当建立连接、退出、查询等操作时。经查看代码，有以下地方记录日志：
** sql/sql_connect.cc
*** check_user(connect和change user都调用此函数)
**** ER_NOT_SUPPORTED_AUTH_MODE和ER_SERVER_IS_IN_SECURE_AUTH_MODE,ER_ACCESS_DENIED_ERROR记录日志：general_log_print COM_CONNECT
**** 认证通过后也记录日志：general_log_print command(COM_CONNECT或COM_CHANGE_USER)
** sql/sql_parse.cc
*** COM_INIT_DB
**** use命令执行成功后记录日志 general_log_write
*** COM_QUERY general_log_write
*** COM_FIELD_LIST general_log_print
*** COM_QUIT general_log_print
*** COM_CREATE_DB general_log_print
*** COM_DROP_DB general_log_write
*** COM_BINLOG_DUMP general_log_print
*** COM_REFRESH general_log_print
*** COM_SHUTDOWN general_log_print
*** COM_STATISTICS general_log_print
*** COM_PROCESS_INFO general_log_print
*** COM_DEBUG general_log_print
** sql/sql_prepare.cc
*** mysqld_stmt_reset general_log_print (COM_STMT_RESET)
*** mysqld_stmt_close general_log_print (COM_STMT_CLOSE)
*** mysql_stmt_get_longdata general_log_print (COM_STMT_SEND_LONG_DATA)
*** Prepared_statement::prepare
**** 存储过程内语句不记录日志 general_log_write COM_STMT_PREPARE
*** Prepared_statement::execute
**** 存储过程内语句不记录日志 general_log_write COM_STMT_EXECUTE
** sql/sp_head.cc
*** sp_instr_stmt::execute
**** general_log_write COM_QUERY
** sql/sql_show.cc
*** mysqld_show_create_db
*** 当权限不够时记录日志 general_log_print COM_INIT_DB
** sql/log_event.cc
*** Query_log_event::do_apply_event
**** If the query was not ignored, it is printed to the general log general_log_write COM_QUERY
*** Xid_log_event::do_apply_event
**** For a slave Xid_log_event is COMMIT general_log_print COM_QUERY
** sql/slave.cc
*** connect_to_master
**** slave重连接master时记录日志 general_log_print COM_CONNECT_OUT
** sql/sql_db.cc
*** mysql_change_db
**** ER_DBACCESS_DENIED_ERROR无权限访问数据库 general_log_print COM_INIT_DB

* 这两个函数内分别调用了LOGGER::general_log_write和LOGGER::general_log_print方法
* LOGGER::general_log_print又调用了LOGGER::general_log_write
* LOGGER::general_log_write根据log_output变量设置的日志输出方式，可能有多个，再调用当前日志句柄中的log_general方法

* Log_to_csv_event_handler::log_general

...

==格式==

===表===
# 当前时间
# 用户名和主机名
## 格式是：priv_user[user] @ host[ip]
# 线程标识
# 命令类型
# 查询语句

===文件===
# 当前时间
# 线程标识
# 命令类型
# 查询语句

==DML操作==
不支持DML操作

===delete===
不能delete日志表

mysql> delete from mysql.general_log;
ERROR 1556 (HY000): You can't use locks with log tables.

避免报错，可rename表名后再操作

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
rename table general_log to general_log_tmp;
delete from general_log_tmp;
rename table general_log_tmp to general_log;
SET GLOBAL general_log = @old_log_state;

====相关代码====
sql/lock.cc

int mysql_lock_tables_check(THD *thd, TABLE **tables, uint count, uint flags)
...
      if ((t->reginfo.lock_type >= TL_READ_NO_INSERT)
          || (thd->lex->sql_command == SQLCOM_LOCK_TABLES))
      {
        my_error(ER_CANT_LOCK_LOG_TABLE, MYF(0));
        DBUG_RETURN(1);
      }
...

==DDL操作==
一些DDL操作不被支持

===drop===
不支持在线drop操作

mysql> drop table mysql.general_log;
ERROR 1580 (HY000): You cannot 'DROP' a log table if logging is enabled

====相关代码====
sql/sql_table.cc

int mysql_rm_table_part2(THD *thd, TABLE_LIST *tables, bool if_exists,
...
  /* Disable drop of enabled log tables, must be done before name locking */
  for (table= tables; table; table= table->next_local)
  {
    if (check_if_log_table(table->db_length, table->db,
                           table->table_name_length, table->table_name, 1))
    {
      my_error(ER_BAD_LOG_STATEMENT, MYF(0), "DROP");
      pthread_mutex_unlock(&LOCK_open);
      DBUG_RETURN(1);
    }
  }
...

===truncate===
支持在线truncate

==存储引擎==
默认用CSV存储引擎。只支持CSV或MyISAM

mysql> set global general_log=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table mysql.general_log engine=innodb;
ERROR 1579 (HY000): This storage engine cannot be used for log tables"

修改为Innodb存储引擎：

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
use mysql
drop table if exists general_log_new;
create table general_log_new like general_log;
alter table general_log_new engine=innodb;
rename table general_log to general_log_old, general_log_new to general_log;
show create table general_log\G
SET GLOBAL general_log = @old_log_state;

====相关代码====
sql/sql_table.cc

bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
...
  /*
    Check if we attempt to alter mysql.slow_log or
    mysql.general_log table and return an error if
    it is the case.
    TODO: this design is obsolete and will be removed.
  */
  if (table_list && table_list->db && table_list->table_name)
  {
    int table_kind= 0;

    table_kind= check_if_log_table(table_list->db_length, table_list->db,
                                   table_list->table_name_length,
                                   table_list->table_name, 0);

    if (table_kind)
    {
      /* Disable alter of enabled log tables */
      if (logger.is_log_table_enabled(table_kind))
      {
        my_error(ER_BAD_LOG_STATEMENT, MYF(0), "ALTER");
        DBUG_RETURN(TRUE);
      }

      /* Disable alter of log tables to unsupported engine */
      if ((create_info->used_fields & HA_CREATE_USED_ENGINE) &&
          (!create_info->db_type || /* unknown engine */
           !(create_info->db_type->flags & HTON_SUPPORT_LOG_TABLES)))
      {
        my_error(ER_UNSUPORTED_LOG_ENGINE, MYF(0));
        DBUG_RETURN(TRUE);
      }

#ifdef WITH_PARTITION_STORAGE_ENGINE
      if (alter_info->flags & ALTER_PARTITION)
      {
        my_error(ER_WRONG_USAGE, MYF(0), "PARTITION", "log table");
        DBUG_RETURN(TRUE);
      }
#endif
    }
  }
...

HTON_SUPPORT_LOG_TABLES 表示该存储引擎是否支持日志表

$ grep -r HTON_SUPPORT_LOG_TABLES *
sql/sql_table.cc:           !(create_info->db_type->flags & HTON_SUPPORT_LOG_TABLES)))
sql/handler.h:#define HTON_SUPPORT_LOG_TABLES      (1 << 7) //Engine supports log tables
storage/myisam/ha_myisam.cc:  myisam_hton->flags= HTON_CAN_RECREATE | HTON_SUPPORT_LOG_TABLES;
storage/csv/ha_tina.cc:  tina_hton->flags= (HTON_CAN_RECREATE | HTON_SUPPORT_LOG_TABLES |

-fin-

MySQL Proxy RW Splitting

MySQL Proxy RW Splitting

=rw-splitting.lua=

==说明==
* MySQL Proxy 0.8.1版本的读写分离脚本
* 下载地址
mysql-proxy-0.8.1-linux-glibc2.3-x86-64bit.tar.gz
* 参考文档
** 读写分离脚本说明
MySQL Proxy RW Splitting: http://forge.mysql.com/wiki/MySQL_Proxy_RW_Splitting
MySQL Proxy learns R/W Splitting: http://jan.kneschke.de/projects/mysql/mysql-proxy-learns-r-w-splitting
MySQL Proxy: more R/W splitting: http://jan.kneschke.de/projects/mysql/mysql-proxy-more-r-w-splitting/
** Lua开发
Lua 5.1 Reference Manual: http://www.lua.org/manual/5.1/manual.html
Programming in Lua (first edition): http://www.lua.org/pil/
** MySQL Proxy脚本开发
MySQL Proxy Scripting: http://dev.mysql.com/doc/refman/5.5/en/mysql-proxy-scripting.html
MySQL Proxy Snippet: http://forge.mysql.com/tools/search.php?t=tag&k=mysqlproxy
MySQL Proxy Dev: http://forge.mysql.com/wiki/MySQL_Proxy_Dev
Advanced LUA Scripting: http://forge.mysql.com/wiki/Advanced_LUA_Scripting
ProxyCookbook: http://forge.mysql.com/wiki/ProxyCookbook
http://blog.suncrescent.net/2008/05/partial-mysql-proxy-api-doc/

==代码==
===1-20行===

  1 --[[ $%BEGINLICENSE%$
  2  Copyright (c) 2007, 2009, Oracle and/or its affiliates. All rights reserved.
  3
  4  This program is free software; you can redistribute it and/or
  5  modify it under the terms of the GNU General Public License as
  6  published by the Free Software Foundation; version 2 of the
  7  License.
  8
  9  This program is distributed in the hope that it will be useful,
 10  but WITHOUT ANY WARRANTY; without even the implied warranty of
 11  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 12  GNU General Public License for more details.
 13
 14  You should have received a copy of the GNU General Public License
 15  along with this program; if not, write to the Free Software
 16  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 17  02110-1301  USA
 18
 19  $%ENDLICENSE%$ --]]
 20

* 声明版权信息
* lua脚本中 "--\[\[" 和 "--\]\]" 之间表示多行注释块

===21-29行===

 21 ---
 22 -- a flexible statement based load balancer with connection pooling
 23 --
 24 -- * build a connection pool of min_idle_connections for each backend and maintain
 25 --   its size
 26 -- *
 27 --
 28 --
 29

* 脚本说明
* lua脚本中 "--" 之后表示单行注释信息

===30-34行===

 30 local commands    = require("proxy.commands")
 31 local tokenizer   = require("proxy.tokenizer")
 32 local lb          = require("proxy.balance")
 33 local auto_config = require("proxy.auto-config")
 34

* require加载MySQL Proxy模块
* 模块文件所在缺省目录是$LUA_HOME/lib/mysql-proxy/lua/proxy/
* 各模块说明如下：
** commands.lua：解析网络包中的MySQL命令
*** 参考 http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#Command_Packet_.28Overview.29
** tokenizer.lua：加载模块mysql.tokenizer；格式化SQL语句。没看懂
** balance.lua：负载均衡
*** function idle_failsafe_rw：获取一个空闲的可读写的连接（可读写的主库或备库）
*** function idle_ro：获取一个空闲的只读的连接（只读备库）
** auto-config.lua：加载模块lpeg，即"Parsing Expression Grammars For Lua"；管理配置参数
*** 语法
**** proxy show config 显示配置
**** proxy set global xxx.yyy=zzz 设置配置
**** proxy save config into "xxx" 保存配置
**** proxy load config from "xxx" 加载配置
*** 参考
**** http://www.inf.puc-rio.br/~roberto/lpeg/
**** http://www.gammon.com.au/scripts/doc.php?general=lua_lpeg
*** 问题
**** [[#proxy set global 报错 ERROR 1064 (42000): You have an error in your SQL syntax]]
**** [[#proxy set global 报错 ERROR 1105 (07000): (lua) proxy.response.errmsg is nil]]
**** [[#proxy save config 保存配置文件大小等于0]]
**** [[#proxy save config 保存配置文件名带双引号]]

===35-46行===

 35 --- config
 36 --
 37 -- connection pool
 38 if not proxy.global.config.rwsplit then
 39         proxy.global.config.rwsplit = {
 40                 min_idle_connections = 4,
 41                 max_idle_connections = 8,
 42
 43                 is_debug = false
 44         }
 45 end
 46

* 设置读写分离的缺省配置参数

===47-55行===

 47 ---
 48 -- read/write splitting sends all non-transactional SELECTs to the slaves
 49 --
 50 -- is_in_transaction tracks the state of the transactions
 51 local is_in_transaction       = false
 52
 53 -- if this was a SELECT SQL_CALC_FOUND_ROWS ... stay on the same connections
 54 local is_in_select_calc_found_rows = false
 55

* 初始化变量
* is_in_transaction：false表示转发查询语句到只读连接上
* is_in_select_calc_found_rows：表示查询语句中是否有"SQL_CALC_FOUND_ROWS"

===56-72行===

 56 ---
 57 -- get a connection to a backend
 58 --
 59 -- as long as we don't have enough connections in the pool, create new connections
 60 --
 61 function connect_server()
 62         local is_debug = proxy.global.config.rwsplit.is_debug
 63         -- make sure that we connect to each backend at least ones to
 64         -- keep the connections to the servers alive
 65         --
 66         -- on read_query we can switch the backends again to another backend
 67
 68         if is_debug then
 69                 print()
 70                 print("[connect_server] " .. proxy.connection.client.src.name)
 71         end
 72

* 函数connect_server说明：获取一个到后端的连接
* 初始化is_debug变量

===75-121行===

 73         local rw_ndx = 0
 74
 75         -- init all backends
 76         for i = 1, #proxy.global.backends do
 77                 local s        = proxy.global.backends[i]
 78                 local pool     = s.pool -- we don't have a username yet, try to find a connections which is idling
 79                 local cur_idle = pool.users[""].cur_idle_connections
 80
 81                 pool.min_idle_connections = proxy.global.config.rwsplit.min_idle_connections
 82                 pool.max_idle_connections = proxy.global.config.rwsplit.max_idle_connections
 83
 84                 if is_debug then
 85                         print("  [".. i .."].connected_clients = " .. s.connected_clients)
 86                         print("  [".. i .."].pool.cur_idle     = " .. cur_idle)
 87                         print("  [".. i .."].pool.max_idle     = " .. pool.max_idle_connections)
 88                         print("  [".. i .."].pool.min_idle     = " .. pool.min_idle_connections)
 89                         print("  [".. i .."].type = " .. s.type)
 90                         print("  [".. i .."].state = " .. s.state)
 91                 end
 92
 93                 -- prefer connections to the master
 94                 if s.type == proxy.BACKEND_TYPE_RW and
 95                    s.state ~= proxy.BACKEND_STATE_DOWN and
 96                    cur_idle < pool.min_idle_connections then
 97                         proxy.connection.backend_ndx = i
 98                         break
 99                 elseif s.type == proxy.BACKEND_TYPE_RO and
100                        s.state ~= proxy.BACKEND_STATE_DOWN and
101                        cur_idle < pool.min_idle_connections then
102                         proxy.connection.backend_ndx = i
103                         break
104                 elseif s.type == proxy.BACKEND_TYPE_RW and
105                        s.state ~= proxy.BACKEND_STATE_DOWN and
106                        rw_ndx == 0 then
107                         rw_ndx = i
108                 end
109         end
110
111         if proxy.connection.backend_ndx == 0 then
112                 if is_debug then
113                         print("  [" .. rw_ndx .. "] taking master as default")
114                 end
115                 proxy.connection.backend_ndx = rw_ndx
116         end
117
118         -- pick a random backend
119         --
120         -- we someone have to skip DOWN backends
121

* 创建一个新的或分配一个已有的MySQL Proxy到MySQL Server后端的连接
* 按backends顺序和下列次序选择：
** 优先选取一个可读写的后端，直到该后端空闲连接数达到最小空闲连接数
** 其次选取一个只读的后端，直到该后端空闲连接数达到最小空闲连接数
** 最后选取第一个可读写的后端
* backends顺序是：先rw后ro；命令行配置中出现的先后顺序
* 问题：分配算法和实际测试结果有差异，原因不明。比如min_idle_connections设置是1，但可以建min_idle_connections+1=2个连接
* connected_clients总是0，原因不明，此变量需要脚本自行赋值维护？
* 当客户端连接数小于min_idle_connections+1时，调试信息中cur_idle_connections总显示为0。超过min_idle_connections+1后才显示出数值，不会新建连接，等于min_idle_connections+1

===122-139行===

122         -- ok, did we got a backend ?
123
124         if proxy.connection.server then
125                 if is_debug then
126                         print("  using pooled connection from: " .. proxy.connection.backend_ndx)
127                 end
128
129                 -- stay with it
130                 return proxy.PROXY_IGNORE_RESULT
131         end
132
133         if is_debug then
134                 print("  [" .. proxy.connection.backend_ndx .. "] idle-conns below min-idle")
135         end
136
137         -- open a new connection
138 end
139

* 没看懂。估计是，如果已建立了连接池，则返回PROXY_IGNORE_RESULT，表示忽略接下来服务器发回的handshake，这样就不会建立起新连接
* 函数connect_server结束

===140-164行===

140 ---
141 -- put the successfully authed connection into the connection pool
142 --
143 -- @param auth the context information for the auth
144 --
145 -- auth.packet is the packet
146 function read_auth_result( auth )
147         if is_debug then
148                 print("[read_auth_result] " .. proxy.connection.client.src.name)
149         end
150         if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
151                 -- auth was fine, disconnect from the server
152                 proxy.connection.backend_ndx = 0
153         elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then
154                 -- we received either a
155                 --
156                 -- * MYSQLD_PACKET_ERR and the auth failed or
157                 -- * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sent
158                 print("(read_auth_result) ... not ok yet");
159         elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then
160                 -- auth failed
161         end
162 end
163
164

* 参考
** http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol
* 问题
** [[#read_auth_result没有显示调试信息]]

===165-171行===

165 ---
166 -- read/write splitting
167 function read_query( packet )
168         local is_debug = proxy.global.config.rwsplit.is_debug
169         local cmd      = commands.parse(packet)
170         local c        = proxy.connection.client
171

* read_query函数
* 解析网络包、语句

===172-174行===

172         local r = auto_config.handle(cmd)
173         if r then return r end
174

* 处理PROXY命令(show,set,save,load)，返回PROXY_SEND_RESULT和结果

===175-188行===

175         local tokens
176         local norm_query
177
178         -- looks like we have to forward this statement to a backend
179         if is_debug then
180                 print("[read_query] " .. proxy.connection.client.src.name)
181                 print("  current backend   = " .. proxy.connection.backend_ndx)
182                 print("  client default db = " .. c.default_db)
183                 print("  client username   = " .. c.username)
184                 if cmd.type == proxy.COM_QUERY then
185                         print("  query             = "        .. cmd.query)
186                 end
187         end
188

* 声明本地变量
* 显示调试信息

===189-204行===

189         if cmd.type == proxy.COM_QUIT then
190                 -- don't send COM_QUIT to the backend. We manage the connection
191                 -- in all aspects.
192                 proxy.response = {
193                         type = proxy.MYSQLD_PACKET_OK,
194                 }
195
196                 if is_debug then
197                         print("  (QUIT) current backend   = " .. proxy.connection.backend_ndx)
198                 end
199
200                 return proxy.PROXY_SEND_RESULT
201         end
202
203         proxy.queries:append(1, packet, { resultset_is_needed = true })
204

* 截取QUIT命令，不发送到后端服务器
* 构造查询队列。命令放入查询队列

===205-255行===

205         -- read/write splitting
206         --
207         -- send all non-transactional SELECTs to a slave
208         if not is_in_transaction and
209            cmd.type == proxy.COM_QUERY then
210                 tokens     = tokens or assert(tokenizer.tokenize(cmd.query))
211
212                 local stmt = tokenizer.first_stmt_token(tokens)
213
214                 if stmt.token_name == "TK_SQL_SELECT" then
215                         is_in_select_calc_found_rows = false
216                         local is_insert_id = false
217
218                         for i = 1, #tokens do
219                                 local token = tokens[i]
220                                 -- SQL_CALC_FOUND_ROWS + FOUND_ROWS() have to be executed
221                                 -- on the same connection
222                                 -- print("token: " .. token.token_name)
223                                 -- print("  val: " .. token.text)
224
225                                 if not is_in_select_calc_found_rows and token.token_name == "TK_SQL_SQL_CALC_FOUND_ROWS" then
226                                         is_in_select_calc_found_rows = true
227                                 elseif not is_insert_id and token.token_name == "TK_LITERAL" then
228                                         local utext = token.text:upper()
229
230                                         if utext == "LAST_INSERT_ID" or
231                                            utext == "@@INSERT_ID" then
232                                                 is_insert_id = true
233                                         end
234                                 end
235
236                                 -- we found the two special token, we can't find more
237                                 if is_insert_id and is_in_select_calc_found_rows then
238                                         break
239                                 end
240                         end
241
242                         -- if we ask for the last-insert-id we have to ask it on the original
243                         -- connection
244                         if not is_insert_id then
245                                 local backend_ndx = lb.idle_ro()
246
247                                 if backend_ndx > 0 then
248                                         proxy.connection.backend_ndx = backend_ndx
249                                 end
250                         else
251                                 print("   found a SELECT LAST_INSERT_ID(), staying on the same backend")
252                         end
253                 end
254         end
255

* 没看懂
** 查看查询语句中是否含有SQL_CALC_FOUND_ROWS或LAST_INSERT_ID
** 如果没有LAST_INSERT_ID，则选一个空闲的只读后端，否则后端不变

===256-272行===

256         -- no backend selected yet, pick a master
257         if proxy.connection.backend_ndx == 0 then
258                 -- we don't have a backend right now
259                 --
260                 -- let's pick a master as a good default
261                 --
262                 proxy.connection.backend_ndx = lb.idle_failsafe_rw()
263         end
264
265         -- by now we should have a backend
266         --
267         -- in case the master is down, we have to close the client connections
268         -- otherwise we can go on
269         if proxy.connection.backend_ndx == 0 then
270                 return proxy.PROXY_SEND_QUERY
271         end
272

* 其它非查询语句，选择一个可读写后端
* 如无可选后端，不做后续操作，直接跳出函数

===273-302行===

273         local s = proxy.connection.server
274
275         -- if client and server db don't match, adjust the server-side
276         --
277         -- skip it if we send a INIT_DB anyway
278         if cmd.type ~= proxy.COM_INIT_DB and
279            c.default_db and c.default_db ~= s.default_db then
280                 print("    server default db: " .. s.default_db)
281                 print("    client default db: " .. c.default_db)
282                 print("    syncronizing")
283                 proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db, { resultset_is_needed = true })
284         end
285
286         -- send to master
287         if is_debug then
288                 if proxy.connection.backend_ndx > 0 then
289                         local b = proxy.global.backends[proxy.connection.backend_ndx]
290                         print("  sending to backend : " .. b.dst.name);
291                         print("    is_slave         : " .. tostring(b.type == proxy.BACKEND_TYPE_RO));
292                         print("    server default db: " .. s.default_db)
293                         print("    server username  : " .. s.username)
294                 end
295                 print("    in_trans        : " .. tostring(is_in_transaction))
296                 print("    in_calc_found   : " .. tostring(is_in_select_calc_found_rows))
297                 print("    COM_QUERY       : " .. tostring(cmd.type == proxy.COM_QUERY))
298         end
299
300         return proxy.PROXY_SEND_QUERY
301 end
302

* 保证后端数据库和客户端数据库一致
* 显示调试信息

===303-332行===

303 ---
304 -- as long as we are in a transaction keep the connection
305 -- otherwise release it so another client can use it
306 function read_query_result( inj )
307         local is_debug = proxy.global.config.rwsplit.is_debug
308         local res      = assert(inj.resultset)
309         local flags    = res.flags
310
311         if inj.id ~= 1 then
312                 -- ignore the result of the USE 
313                 -- the DB might not exist on the backend, what do do ?
314                 --
315                 if inj.id == 2 then
316                         -- the injected INIT_DB failed as the slave doesn't have this DB
317                         -- or doesn't have permissions to read from it
318                         if res.query_status == proxy.MYSQLD_PACKET_ERR then
319                                 proxy.queries:reset()
320
321                                 proxy.response = {
322                                         type = proxy.MYSQLD_PACKET_ERR,
323                                         errmsg = "can't change DB ".. proxy.connection.client.default_db ..
324                                                 " to on slave " .. proxy.global.backends[proxy.connection.backend_ndx].dst.name
325                                 }
326
327                                 return proxy.PROXY_SEND_RESULT
328                         end
329                 end
330                 return proxy.PROXY_IGNORE_RESULT
331         end
332

* 处理COM_INIT_DB命令的返回结果
* 忽略其它语句的结果

===333-348行===

333         is_in_transaction = flags.in_trans
334         local have_last_insert_id = (res.insert_id and (res.insert_id > 0))
335
336         if not is_in_transaction and
337            not is_in_select_calc_found_rows and
338            not have_last_insert_id then
339                 -- release the backend
340                 proxy.connection.backend_ndx = 0
341         elseif is_debug then
342                 print("(read_query_result) staying on the same backend")
343                 print("    in_trans        : " .. tostring(is_in_transaction))
344                 print("    in_calc_found   : " .. tostring(is_in_select_calc_found_rows))
345                 print("    have_insert_id  : " .. tostring(have_last_insert_id))
346         end
347 end
348

* 参考：http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#OK_Packet
* 检查是否在事务内，是否有insert_id。设置backend_ndx=0，下次重新选取后端连接？

===349-364行===

349 ---
350 -- close the connections if we have enough connections in the pool
351 --
352 -- @return nil - close connection
353 --         IGNORE_RESULT - store connection in the pool
354 function disconnect_client()
355         local is_debug = proxy.global.config.rwsplit.is_debug
356         if is_debug then
357                 print("[disconnect_client] " .. proxy.connection.client.src.name)
358         end
359
360         -- make sure we are disconnection from the connection
361         -- to move the connection into the pool
362         proxy.connection.backend_ndx = 0
363 end
364

* 不懂

==问题==

===ERROR 1047 (08S01): Unknown command===
现象：
* 用MySQL Proxy 0.8.1缺省的rw-splitting脚本，客户端连接多次，当每个backend都建立起一定空闲连接（连接数都达到min_idle_connects+1，原因不清）后，再次连接时报错"ERROR 1047 (08S01): Unknown command"。如下：

$ ./mysql -h 127.0.0.1 -P 4040 -u yyyy -pxxxx -e "proxy show config"
+---------+----------------------+-------+---------+
| module  | option               | value | type    |
+---------+----------------------+-------+---------+
| rwsplit | min_idle_connections | 1     | number  |
| rwsplit | is_debug             | true  | boolean |
| rwsplit | max_idle_connections | 4     | number  |
+---------+----------------------+-------+---------+
$ ./mysql -h 127.0.0.1 -P 4040 -u yyyy -pxxxx -e "proxy show config"
+---------+----------------------+-------+---------+
| module  | option               | value | type    |
+---------+----------------------+-------+---------+
| rwsplit | min_idle_connections | 1     | number  |
| rwsplit | is_debug             | true  | boolean |
| rwsplit | max_idle_connections | 4     | number  |
+---------+----------------------+-------+---------+
$ ./mysql -h 127.0.0.1 -P 4040 -u yyyy -pxxxx -e "proxy show config"
ERROR 1047 (08S01): Unknown command

* MySQL数据库版本是5.5的，改成5.1问题消失

原因：
* 好像认证有问题，5.5下read_auth_result了2次。原因待查

If a long-password capable client tries to authenticate to a server that supports long passwords, but the user password provided is actually short, read_auth_result() will be called twice. The first time, auth.packet:byte() will equal 254, indicating that the client should try again using the old password protocol. The second time time read_auth_result()/ is called, auth.packet:byte() will indicate whether the authentication actually succeeded.

解决：
* 无
* 不用5.5版本的数据库

===read_auth_result没有显示调试信息===
现象：
* is_debug设置为true，然而read_auth_result没有显示出debug信息

原因：
* 脚本问题，函数read_auth_result中的变量is_debug没有赋值

解决：
* 在147行之前增加如下代码：

        local is_debug = proxy.global.config.rwsplit.is_debug

* 修改脚本后即可生效，不用重启MySQL Proxy

===proxy set global 报错 ERROR 1064 (42000): You have an error in your SQL syntax===
现象：
* 客户端执行命令"proxy set global xxx.yyy = zzz"，设置MySQL Proxy参数，报语法错误，如下：

mysql> proxy set global rwsplit.min_idle_connections = 2;
ERROR 1064 (42000): 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 'proxy set global rwsplit.min_idle_connections = 2' at line 1

原因：
* auto-config.lua脚本问题。不能处理带下划线的参数名称。

解决：
* 修改脚本lib/mysql-proxy/lua/proxy/auto-config.lua
将

local literal = l.R("az", "AZ") ^ 1

改成

local literal = l.R("az", "AZ", "__") ^ 1

* 然后重启MySQL Proxy进程

===proxy set global 报错 ERROR 1105 (07000): (lua) proxy.response.errmsg is nil===
现象：
* 设置is_debug=false报错，如下：

mysql> proxy set global rwsplit.is_debug = false;
ERROR 1105 (07000): (lua) proxy.response.errmsg is nil

原因：
* auto-config.lua脚本问题。当参数值等于false时，parse_value函数返回false，导致"if not val then"判断出错

解决：
* 修改lib/mysql-proxy/lua/proxy/auto-config.lua，handle函数代码
将

if not val then

改为

if val == nil then

* 然后重启MySQL Proxy服务

===proxy save config 保存配置文件大小等于0===
现象：
* proxy save config 命令保存的配置文件，文件大小等于0，要等待一段时间，或重启MySQL Proxy后，其大小才不等于0

-rw-r-----  1 oracle oinstall    0 Jul  4 15:36 "a.log"

原因：
* auto-config.lua脚本问题。文件写操作之后，没有刷新缓冲区

解决：
* 修改lib/mysql-proxy/lua/proxy/auto-config.lua，save函数代码
在

file:write(content)

之后，增加

file:flush(content)

===proxy save config 保存配置文件名带双引号===
现象：
* proxy save config 命令保存的配置文件，文件名包括双引号

-rw-r----- 1 oracle oinstall  186 Jul  4 15:59 "a"

原因：
* auto-config.lua脚本问题。文件名匹配包括双引号

解决：
* 修改lib/mysql-proxy/lua/proxy/auto-config.lua代码
将

local string_quoted  = l.P("\"") * ( 1 - l.P("\"") )^0 * l.P("\"") -- /".*"/

改成

local string_quoted  = l.P("\"") * l.C(( 1 - l.P("\"") )^0) * l.P("\"") -- /".*"/

将

                l.Ct( l.Cc("string") * l.C(string_quoted) +
...
...
         (SAVE / "SAVE" * CONFIG * WS^1 * INTO * l.C(string_quoted)) +
         (LOAD / "LOAD" * CONFIG * WS^1 * FROM * l.C(string_quoted))) * -1)

改成

                l.Ct( l.Cc("string") * string_quoted +
...
...
         (SAVE / "SAVE" * CONFIG * WS^1 * INTO * string_quoted) +
         (LOAD / "LOAD" * CONFIG * WS^1 * FROM * string_quoted)) * -1)

-fin-

ODI FAQ

去年DW项目实施时写的一些常见问题，主要针对ODI 10g版本，内容较陈旧，仅供参考

===一个变量可以在多个Scenario中使用而不产生冲突吗?===
可以

Recommendations For Working With ODI Variables In Startscen And OdiStartScen Calls ID 423737.1
https://support.oracle.com/CSP/main/article?cmd=show&id=423737.1&type=NOT

If the same Variable is used in multiple simultaneously running Scenarios, it must have the 'Action' parameter set to 'Not Persistent' to avoid Variable value collision from concurrent Scenarios.

===如何处理目录下的所有文件===

ODI Series – Processing all files in a directory: http://john-goodwin.blogspot.com/2009/06/odi-series-processing-all-files-in.html
How to refresh ODI variables from file – Part 1 – Just one value: http://odiexperts.com/?p=243
How to refresh ODI variables from file – Part 2 – Getting all lines, once at time: http://odiexperts.com/?p=273
Getting one or several unknown files from a directory: http://odiexperts.com/?p=1426

===如何删除ODI日志===
How To Avoid Saturating The ODI Log Tables ID 423934.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=423934.1
Session And Scenario Execution Log Tables Used By ODI ID 424663.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=424663.1
Programming ODI To Automatically Purge The Operator Journal ID 423839.1 https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=423839.1
How To Manually Delete A Scenario And Its Related Reports In The ODI Repository With SQL ID 424661.1 https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=424661.1
ODI snippets: Purge Log and shrink space: http://www.business-intelligence-quotient.com/?p=539

===ODI User Function怎么用?===
ODI User Functions: A Case Study: http://blogs.oracle.com/dataintegration/2009/09/odi_user_functions_a_case_stud.html
http://john-goodwin.blogspot.com/2009/06/odi-series-quick-look-at-user-functions.html

===如何并行执行过程===
Parallel Processing in ODI: http://blogs.oracle.com/dataintegration/2009/11/parallel_processing_in_odi.html
How To Run ODI Processes In Parallel And Using OdiWaitForChildSession ID 443858.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=443858.1
Asynchronous -Parallel Execution: http://odiexperts.com/?p=1180

===ODI的学习资源有哪些===
Oracle Data Integrator: http://www.oracle.com/technology/products/oracle-data-integrator/index.html
Oracle Data Integrator Release 10.1.3.6 Documentation Library: http://download.oracle.com/docs/cd/E15985_01/index.htm
Oracle Data Integrator Downloads: http://www.oracle.com/technology/software/products/odi/index.html
Oracle Data Integrator应用指南: http://www.docin.com/p-32319219.html
Oracle Data Integrator/Sunopsis, Releases and Patches ID 456313.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=456313.1
My Oracle Support searching keyword odi: http://support.oracle.com/
Data Integration Forum: http://forums.oracle.com/forums/forum.jspa?forumID=374
Developing a Knowledge Module in Oracle Data Integrator: http://www.oracle.com/technology/pub/articles/bethke-odi.html
ODI Resources for Beginners: Getting Started with Oracle Data Integrator: http://www.business-intelligence-quotient.com/?p=379
Oracle Data Integration Resource Kit. Included is the State of the Data Integration Market White Paper, in addition to additional key Data Integration resources.: http://www.oracle.com/webapps/dialogue/dlgpage.jsp
Oracle by Example Series: Oracle Data Integrator: http://www.oracle.com/technology/obe/fusion_middleware/odi/index.html
http://www.oracle.com/technology/obe/fusion_middleware/ODI/index.html
Data Integration and Management: http://blogs.oracle.com/dataintegration/
ODI EXPERTS: http://odiexperts.com/
More to life than this...: http://john-goodwin.blogspot.com/
BI-Quotient: http://www.business-intelligence-quotient.com/
Rittman Mead: http://www.rittmanmead.com
Business Intelligence – Oracle: http://oraclebizint.wordpress.com/category/oracle-data-integrator/

Oracle Czech BI/DW Blog: http://bidwcz.blogspot.com/
Technology & Other Worldly Issues: http://askankit.blogspot.com/

https://dbinfo.no.sohu.com:4443/apex/p?n=8247611144080585
https://dbinfo.no.sohu.com:4443/apex/p?n=8247701710096829

Note: The repository structure changes slightly between major versions and in particular from 11g onwards.
In this case, it is recommended to use the SDK instead of querying the repository tables directly.

===如何对数据源UNION操作===
Is It Possible To Use A UNION Operation Of Two Datastores As A Source For An ODI Integration Interface? ID 423731.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=423731.1
KM FOR UNION BASED ON IKM SQL CNTRL APPEND: http://odiexperts.com/?p=1164

===ODI授权===
Q:

开发想修改md_xxx中dm_agent，想定义为缓慢变化维，我用security manager通过profile给他授予了Object，Model Folder,Model,Sub-Model,Datastore 的所有权限，然后把md_xxx拖到了该用户下instances目录下，指定repository为wk_dev。增加这些权限后，开发说还是报错：com.sunopsis.core.SecurityAccessException: You are not authorized to Edit the Object:SNP_JOIN : DM_AGENT_FK_CHANNEL / 6001
难道我还得把相应的datastore也拖到用户下? ODI授个权怎么就这么麻烦呢? 这种操作到底需要授予哪些权限?

A:

we don't know

===如何用动态文件名===
Using A Dynamically Specified File Name Or Directory Name With ODI ID 423635.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=423635.1
Using Parameters in ODI: The Dynamic File Name Example: http://blogs.oracle.com/dataintegration/2009/04/using_parameters_in_odi_the_dy_1.html

===Operator中如何显示出变量值?===
Find the Value of the Variable in Operator ?: http://odiexperts.com/?p=855
ODI Variables and the Operator Module: http://www.business-intelligence-quotient.com/?p=762

One Comment on “ODI Variables and the Operator Module”

  1.
    #1 Craig Stewart said at 3:18 pm on March 17th, 2010:

    Uli
    Another great article – another snippet to follow-up. If the variable has been passed into the execution as a parameter, then if you use odiRef.getSession(”SESS_PARAMS” ) it will printout the values of the parameters. To make this readable in the execution, I usually put in a procedure with a Java Beanshell step, with the code:
    /* */
    As it is in /* */ comments, it is ignored, and does not look like an error/kludge, but prints out neatly in the log.
    Shame there is no similar thing for the variables set dynamically!
    Craig

Using Jython Scripting Features In ODI FAQ ID 566475.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=566475.1#aref78

10. Displaying ODI Variables Values In ODI Operator Log

Is there any technique whereby it is possible, in the ODI Operator Log, to display the values of ODI Variables which have been initialized from runtime parameters when launching Scenarios?
Such a technique would allow the recording of Variable values during runtime and would be useful for execution audit operations.

Two alternative solutions are possible:

In the case of OdiStartScen (SnpsStartScen) tool, it is possible to specify the name of the Session with the "-SESSION_NAME" parameter. In this case, pass the value of the ODI Variable to this parameter for display in ODI Journal.

A Jython script step such as the following, in a Knowledge Module, will allow the display of the ODI Variable value in the Execution tab of the step :
a = 'Table = '+ '#YOURVARIABLE'
raise(a)


Such a Jython script will require the "Ignore errors" checkbox to be checked in the Knowledge Module step. Do not forget that such an Integration Interface must be called from an ODI Package including steps which Declare and Refresh the ODI Variable.

Variable values cannot be displayed in this case because the Agent records data in ODI Operator Log before the ODI process that substitutes Variables by their values, and before sending the values to the technology be be directly used or bound.

===如何重新生成scenario===
ODI: Automating deployment of scenarios to production in Oracle Data Integrator: http://www.business-intelligence-quotient.com/?p=340
What Will Each Generation Mode (Replace, Re-generate, Creation) Of ODI Project "Generate All Scenarios" Option Do? ID 555709.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=555709.1
Generate Multiple Scenarios: http://odiexperts.com/?p=456
In ODI What Is The Difference Between Generation And Regeneration Of A Scenario ID 733103.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=733103.1

How To Ensure That The Regeneration Of An ODI Scenario Is Successful? ID 804583.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=804583.1

===生成场景报错ORA-12899===
Q:

生成场景时报如下错误
java.sql.SQLException: ORA-12899: value too large for column "W_DEV"."SNP_SCEN_STEP"."VAR_VALUE" (actual: 265, maximum: 250)
如何解决？

A:

变量赋值不能超过250字符。可以将一个大的变量分成多个小的变量，最后再拼到一起。

===用OdiFileWait报错===
Q:

程序中使用OdiFileWait检查文件是否存在，文件可以被ls看到，目录权限drwxr-xr-x，文件权限-rw-r--r--，但是OdiFileWait总报错说No File Found.

A:

# TIMEOUT时间太短，应该设置长一些，比如10000ms
# 文件名PATTERN中不能带目录名，比如#v_dirname/#v_filename，只能是纯文件名
# 改用别的方式检查文件是否存在，比如OdiOSCommand, sh -c "test -f #v_dirname/#v_filename"

===SQLLDR导数据的字段顺序出错===
Q:

用LKM File to Oracle (SQLLDR)导数据，报错字段格式不对。检查日志和生成的控制文件，发现控制文件中字段的顺序变了，跟文件中不一致

A:

因为在Interface配置的Target Datastore有些字段execute on source,有些execute on staging。KM生成的控制文件中，字段顺序自动发生了变化，execute on source的字段在前，execute on staging的在后。所以相关字段统一使用execute on source或staging...，顺序才不会变。

===IKM插入数据报错 ORA-01031: insufficient privileges===
Q:

IKM SQL Control Append在Insert new rows这步报错

1031 : 42000 : java.sql.SQLException: ORA-01031: insufficient privileges

java.sql.SQLException: ORA-01031: insufficient privileges

执行的语句是

insert /*+ append */ into  ods.ODS_ORACLE_TABXXX
(
….
from  ODI_WORK.I$_ODS_ORACLE_TABXXX

直接用sqlplus以odi work用户在ods数据库上执行同样语句，报错

    ,ods.seq_ods_oracle_tabxxx_id.nextval,
          *
ERROR at line 88:
ORA-01031: insufficient privileges

A:

ods库上的odi work用户没有相应权限，需要手工授予

grant select any sequence to ODI_WORK;

===如何实现反连接?===
Q:

ODI中如何实现not in, not exists
比如:

select * from t1 where not exists (select null from t2 where t2.c1=t1.c1)

A:

How To Set Up Negative Join Operations In An ODI Integration Interface ? ID 742144.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=742144.1
Using Oracle Outer Join Operations Such As LIKE, IN, NOT IN, IS NULL ...In ODI Integration Interfaces. ID 424034.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=424034.1

比如

select * from t1 where not exists (select null from t2 where t2.c1=t1.c1)

用left outer join实现:

Interface->Diagram->Sources

* t1 left outer join t2
** 注意不能选中left outer join复选框, 必须手工写(+)号
**Implementation填t1.c1=t2.c1(+), 设置为execute on staging
* t2增加filter, Implementation填t2.c1 is null, 设置为execute on staging

===刷新变量出错No value to affect to this variable for DefDate===
Q:

Package中刷新变量，报错Error: No value to affect to this variable for DefDate
意思是说select刷新语句没有返回结果。
然而数据库中运行select语句可以查到记录，有返回结果。

Package是按照Best practice，先声明变量，再刷新的，使用上应该没有错。

刷新语句是:

select to_char(t1_id) from admin_odi.t1
where name='#v_t1_name'

* 将where子句改成where name='p_city_day'，也报同样错。
* 如果注释掉where子句，改成select to_char(p1_id) from admin_odi.t1，则刷新成功，取得第一条记录的结果。

A:

* 原因不明。
* 删掉Diagram中变量声明和刷新的步骤，重新定义这2步，即可。

===调用场景参数中带双引号的转义===
Q:

用如下命令调用一个场景失败，报Unmatching quotes错误

OdiStartScen "-SCEN_NAME=T1_INVOKER" "-SCEN_VERSION=001" "-TEST_XIEWEN.v_t1_name=p_city_day" "-TEST_XIEWEN.v_scen_name=P_CITY_DAY" "-TEST_XIEWEN.v_scen_params=\"-ODS_FROM_ONLINE_CITY.v_start_date=#TEST_XIEWEN.v_start_date\" \"-ODS_FROM_ONLINE_CITY.v_end_date=#TEST_XIEWEN.v_end_date\" \"-ODS_FROM_ONLINE_CITY.v_date_format=#TEST_XIEWEN.v_date_format\""

应如何转义双引号？

A:

现在用的方法是，先用一段特殊字符代表双引号，在被调用的场景中再将特殊字符替换回双引号

调用场景时，特殊字符\\u0022代表双引号:

OdiStartScen "-SCEN_NAME=T1_INVOKER" "-SCEN_VERSION=001" "-TEST_XIEWEN.v_t1_name=p_city_day" "-TEST_XIEWEN.v_scen_name=P_CITY_DAY" "-TEST_XIEWEN.v_scen_params=\\u0022-ODS_FROM_ONLINE_CITY.v_start_date=#TEST_XIEWEN.v_start_date\\u0022 \\u0022-ODS_FROM_ONLINE_CITY.v_end_date=#TEST_XIEWEN.v_end_date\\u0022 \\u0022-ODS_FROM_ONLINE_CITY.v_date_format=#TEST_XIEWEN.v_h1\\u0022"

场景中再替换回双引号：

select replace(q'{#v_scen_params}','\\u0022','"') from dual

===改resource name后interface取的还是旧表名===
Q:

为了测试用，将一个表data store的resource name改成另一个临时建的新表名，点refresh number of rows显示行数也和新表名相符, 然而运行interface，取到的还是旧表数据。为什么？

A:

* LKM Oracle to Oracle (DBLINK)_corp使用了odiRef.getSrcTablesList("", "[SCHEMA].[TABLE_NAME]获得源表表名
** TABLE_NAME表示逻辑表名,RES_NAME才表示物理表名
** TABLE_NAME Logical name of the source datastore
** RES_NAME Physical access name of the resource.......
* 所以修改resource name不管用，必须修改data store的名字。

===Historize变量没取到最新值===
Q:

有一个Historize变量v_etl_sysdate每天都刷新为当天的日期，通过变量的历史可以看到。

package查看这个变量，得到的不是最后一次刷新的值(2010-07-16)，是历史记录中的第一个值(2010-04-09)。

SQL> select * from SNP_VAR_DATA where var_name='S2ODS.v_etl_sysdate' order by FIRST_DATE;

VAR_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
CONTEXT_CODE                                          I_VAL VAR_D
---------------------------------------------------------------------- ---------- -------------------
VAR_V
------------------------------------------------------------------------------------------------------------------------------------------------------
  VAR_N FIRST_DATE      FIRST_I_USER LAST_DATE      LAST_I_USER I_TXT_VAR_T
---------- ------------------- ------------ ------------------- ----------- -----------
S2ODS.v_etl_sysdate
EMPDATAB                                              6591
2010-04-09
      2010-04-09 00:29:59        2010-04-09 00:29:59

S2ODS.v_etl_sysdate
EMPDATAB                                            21382
2010-04-10
      2010-04-10 00:29:59        2010-04-10 00:29:59

...
...
S2ODS.v_etl_sysdate
EMPDATAB                                            1141971
2010-07-15
      2010-07-15 01:00:00        2010-07-15 01:00:00

S2ODS.v_etl_sysdate
EMPDATAB                                            1142420
2010-07-16
      2010-07-16 01:00:00        2010-07-16 01:00:00


98 rows selected.

SQL>

A:

* 直接执行package能取到最新值。
* 执行scenario, 如果生成scenario时，选择了v_etl_sysdate做参数，则以后运行scenario该参数默认值都是历史第一个值。
* 生成scenario不选择该变量做参数，执行也可得到最新值。

===ODI 11gR1===
document: http://download.oracle.com/docs/cd/E14571_01/odi.htm
download: http://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=11571971

===如有error/discard记录，LKM SQLLDR导入失败===
Q:

如果有error或discard记录，LKM SQLLDR导入失败

A:

sqlloader not load file in oracle when get invalid rows.: http://forums.oracle.com/forums/thread.jspa?threadID=1043579

This is a bug 8560194 with ODI LKM File to Oracle (SQLLDR) and it has not been resolved yet.
SQLLDR if successful returns 0
SQLLDR if unsuccessful returns 1
SQLLDR if successful but even 1 record erred out to .bad or .dsc file returns 2

So, for ODI anything that is not 0 is an error.

To resolve this bug, you will have to customize this KM and change the step "Invoke SQLLDR" to selectively handle each error condition.

Duplicate the KM.
Goto the step Call sqlldr. and change the technology to Jython.

Replace the existing code with the following:

import os
retVal = os.system(r'sqlldr control=<%=snpRef.getSrcTablesList("", "[WORK_SCHEMA]/[TABLE_NAME].ctl", "", "")%> log=<%=snpRef.getSrcTablesList("", "[WORK_SCHEMA]/[TABLE_NAME].log", "", "")%> userid=<%=snpRef.getInfo("DEST_USER_NAME")%>/<%=snpRef.getInfo("DEST_PASS")%>@<%=snpRef.getInfo("DEST_DSERV_NAME")%> > <%=snpRef.getSrcTablesList("", "[WORK_SCHEMA]/[TABLE_NAME].out", "", "")%>')

if retVal == 1 or retVal > 2:
  raise 'SQLLDR failed. Please check the <%=snpRef.getSrcTablesList("", "[WORK_SCHEMA]/[TABLE_NAME].log", "", "")%> for details '


Hope that helps

===显示Session运行状态有问题===
Q:

Operator日志显示某Session总是Running状态，在后台查询已无相应数据库会话

A:

该会话可能是从本机No agent发起的，当会话运行中，退出了客户端(Designer,Operator)。客户端退出，会话也随之终止，但会话状态不会更新到ODI系统表。

===过程中使用绑定变量，报错Missing parameter===
Q:

Procedure中一条语句使用到了bind variable:

begin
...
UPDATE <%=odiRef.getOption("TABLE_NAME")%>
  SET STATUS=<%=odiRef.getOption("T1_STATUS")%>
  WHERE T1_ID=<%=odiRef.getOption("T1_ID")%>;
...
end;

P1_ID赋值为":ADMIN_ODI.v_t1_ID"，开头的冒号表示使用绑定变量

执行时出错:

com.sunopsis.sql.SnpsMissingParametersException: Missing parameter
  at com.sunopsis.sql.SnpsQuery.completeHostVariable(SnpsQuery.java)
  at com.sunopsis.sql.SnpsQuery.updateExecStatement(SnpsQuery.java)
...

日志显示，最后生成的语句是:

begin
...
UPDATE #ADMIN_ODI.v_admin_odi_t1
  SET STATUS=2
  WHERE T1_ID=:ADMIN_ODI.v_t1_ID;
...
end;

A:

ODI把UPDATE语句最后的分号当成变量名的一部分，所以找不到变量，就报错了。

分号换行写，或前面加空格与变量名分开，即可。

===OdiOSCommand双连字符问题===
Q:

* OdiOSCommand调用命令sh -c "rsync -q --dry-run ...."
* 从Operator操作日志观看生成的命令，double hyphen及其后字符都没有了
* 将双连字符改成\u002d\u002d，生成命令没错，执行报错"命令返回:2"
* 检查出错原因，好像还是因为双连字符后被截断了，导致命令不完整:

sh: -c: line 0: unexpected EOF while looking for matching `"'
sh: -c: line 1: syntax error: unexpected end of file

A:

OdiOutFile先将执行的命令写到一个脚本内，然后OdiOSCommand调用此脚本

===SNP_SESSION.SESS_STATUS字段代表什么含义?===
* D = Done
* W = Waiting
* E = Error
* R = Running
* M = Warning
* Q = Queued

===SQLLDR LKM如何实现只选择几列导入===
Q:

* 一个文件有A,B,C 3列，数据库表中有B,C 2列
* interface mapping只映射了B,C列, km生成的CTL文件内也只有这2列, 于是字段不匹配，sqlldr导入失败

A:

对所有没有mapping的字段，建filter, 填字段名 is not null or 字段名 is null

===Scheduler Agent和Work Repository是一一对应的吗===
Q:

odiparam.sh有个参数ODI_SECU_WORK_REP，表示agent连接哪个工作资料库

10g中，Scheduler Agent在启动时连接该工作资料库，获取scheduler定时任务

11g，情况好像有所不同，启动agent后，日志显示:

2010-08-24 20:08:38.177 NOTIFICATION ODI-1128 Agent pa_emods_dev is starting. Application Server: STANDALONE. Agent Version: 11.1.1.3.0 - 23/06/2010
. Port: 21911. JMX Port: 22911
2010-08-24 20:09:01.667 NOTIFICATION ODI-1136 Starting Schedulers on Agent pa_ods_dev
2010-08-24 20:09:01.853 NOTIFICATION ODI-1111 Agent pa_emods_dev started. Agent version: 11.1.1.3.0 - 23/06/2010. Port: 21911. JMX Port: 22911.
2010-08-24 20:09:01.907 NOTIFICATION ODI-1137 Scheduler started for work repository REPWPROD on Agent pa_ods_dev
2010-08-24 20:09:02.012 NOTIFICATION ODI-1137 Scheduler started for work repository REPWDEV on Agent pa_ods_dev

貌似连接了所有工作资料库，一个agent就管理了所有scheduler任务

odiparam.sh对该变量的注释也有所变化：

# The following work repository name variable is required by startcmd (when the specified
# command needs to connects to the work repository), startscen, and restartsession scripts.
# The agent startup and agentstop programs do not need this variable.
#
#  ODI_SECU_WORK_REP
#    Name of the Work Repository to connect. This Work Repository must
#    be attached to the master repository.

A:

测试了下，好像是这样
* 10g scheduler agent从odiparam.sh指定的work repository取出context和logical agent等于当前physical agent的任务
* 11g agent从所有的work repositories取出context和logical agent等于当前physical agent的任务

7.2.2 Agent Startup and Shutdown Cycle: http://download.oracle.com/docs/cd/E14571_01/core.1111/e10106/odi.htm#ASHIA4972

When the Oracle Data Integrator agent starts, it first reads its configuration, which includes master repository connection information. Then the agent connects to each of the work repositories attached to this master repository and removes stale sessions. Stale sessions are sessions that are incorrectly indicated in the work repository as running on this given agent. Stale sessions may result from an agent being stopped without being able to stop these sessions gracefully. As the agent restarts, it identifies the stale sessions and moves them to an error state.

From that point, the agent can retrieve and compute the schedules available for it in each work repository. Once this phase is complete, the agent waits for incoming sessions requests to start their processing, and is also able to start sessions based on its schedules.

===如何使用子查询===
Using subqueries in Oracle Data Integrator (ODI) interfaces for complex data integration requirements: http://www.business-intelligence-quotient.com/?p=621
Subselect, derived tables, and subqueries in ODI 11G: http://www.business-intelligence-quotient.com/?p=1045

===生成场景报错An object will be in an unidentified state===
Q:

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=FAQ&id=471564.1#aref17

2. "An object will be in an unidentified state...." Message Is Randomly Displayed
The following message is displayed randomly:
"An object will be in an unidentified state (it is in the state Not found in the database and Update is currently being performed on it) " for example, when refreshing an Oracle Data Integrator Variable, running a Scenario...

Such a problem has been attributed to the ODI Log related tablespaces being full.

Workarounds are the following:

Increase the size of the appropriate tablespaces containing the ODI Work Repositories.
Purge the Log tables in ODI Log (Operator).

A:

* 删除日志

* 关了重新打开，有时也能好

===MySQL建temporary indexes的问题===
Q:

LKM建临时索引使用如下语句:

Create <%=odiRef.getTemporaryIndex ("INDEX_TYPE_CLAUSE") %> Index <%=odiRef.getObjectName(odiRef.getTemporaryIndex ("IDX_NAME"),"W") %> on <%=odiRef.getTemporaryIndex ("COLL_NAME")%>
<%=odiRef.getTemporaryIndexColList( "(", " [CX_COL_NAME] ", ", ", ")" )%>

生成的SQL语句是:

create ... index aaa.ix$_bbbb on .....;

索引名之前带上了数据库名

MySQL不支持这样写，应去掉数据库名:aaa.

所以将KM写为

Create <%=odiRef.getTemporaryIndex ("INDEX_TYPE_CLAUSE") %> Index ", "W").substring(odiRef.getCatalogName("W").length()+1)?> on <%=odiRef.getTemporaryIndex ("COLL_NAME")%>
<%=odiRef.getTemporaryIndexColList( "(", " [CX_COL_NAME] ", ", ", ")" )%>

A:

good

===OdiSqlUnload 报错内存不够===
Q:

使用OdiSqlUnload导出数据，大约28万条记录，报OutOfMemoryError

java.lang.OutOfMemoryError: Java heap space
  at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:1585)
  at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1409)
  at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2886)
  at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:476)
  at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2581)
  at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1757)
  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2171)
  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
  at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
  at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
  at com.sunopsis.sql.SnpsQuery.executeQuery(SnpsQuery.java:602)
  at com.sunopsis.dwg.tools.SqlUnload.actionExecute(SqlUnload.java:327)
  at com.sunopsis.dwg.function.SnpsFunctionBase.execute(SnpsFunctionBase.java:273)
  at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execIntegratedFunction(SnpSessTaskSql.java:3185)
  at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execIntegratedFunction(SnpSessTaskSql.java:1414)
  at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java:2785)
  at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2515)
  at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:534)
  at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:449)
  at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1954)
  at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1818)
  at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:559)
  at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:224)
  at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:481)
  at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1040)
  at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:114)
  at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
  at java.lang.Thread.run(Thread.java:619)

A:

增加odi agent java虚拟机内存

"java heap OutOfMemory" Error When Loading A Large Amount Of Data From Mysql With ODI ID 737309.1: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id=737309.1

This issue is due to a MySQL JDBC Driver issue.

When selecting a large amount of data, the MySQL JDBC Driver loads the entire ResultSet returned by the select to the JVM's memory.

This seems to be the case for any version of MySQL JDBC Driver.

* http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-implementation-notes.html

ResultSet

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and can not allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, you need to create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
        java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this any result sets created with the statement will be retrieved row-by-row.

* 经试验,odiunload 参数fetchsize改小，比如改成10，能成功

===索引===
Q:

建索引

A:

create index w_dev.i_SNP_SESSION_psn on w_dev.SNP_SESSION(PARENT_SESS_NO) online;
create index w_prod.i_SNP_SESSION_psn on w_prod.SNP_SESSION(PARENT_SESS_NO) online;
...

create index w_dev.i_SNP_OBJ_STATE_IO on w_dev.SNP_OBJ_STATE(I_INSTANCE,I_OBJECT) online;
create index w_prod.i_SNP_OBJ_STATE_IO on w_prod.SNP_OBJ_STATE(I_INSTANCE,I_OBJECT) online;
...

create index w_prod.i_SNP_SESSION_an_ss on w_prod.SNP_SESSION(AGENT_NAME,SESS_STATUS) online;
create index w_dev.i_SNP_SESSION_an_ss on w_dev.SNP_SESSION(AGENT_NAME,SESS_STATUS) online;

===导入报错 ORA-00001: AK_SNP_GRP_STATE===
Q:

导入一个项目时报错：

java.sql.SQLException: ORA-00001: unique constraint (xxxx.AK_SNP_GRP_STATE) was happend
...

改用utf-8格式导出导入，导入报错

... integrity constraint (xxxxx.FK_TXT) violated - Parent Key are not found.
...

A:

因有人以中文系统创建了项目，项目中带有中文字符，如Markers是中文名称，如：优先级、进度、笑脸

将中文标识改为英文后，导出导入成功

===ODI Sendmail tool 发中文显示乱码===
Q:

用ODI sendmail tool发中文邮件，显示为乱码

A:

标题和内容编码不对，默认用了操作系统的语言设置

方法1：ODI Agent环境变量LANG设置GBK，LANG=zh_CN.gbk，然后重启agent

方法2：自己编写jython程序，支持编码，参考Unprintable And UTF-8 Characters When Sending Emails With The ODI SendMail Tool ?: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id=423953.1。未能实现

===ODI Agent不断重启===
Q:

按问题[#ODI Sendmail tool 发中文显示乱码]所述，设置LANG=zh_CN.gbk后，odi agent反复重启

A:

经检查，是/etc/init.d/odiagentctl condstart重启了进程

LANG改成zh_CN后，ping日志内容变成中文，脚本无法识别中文，因此误判，导致重启

只要将

grep -q -- "Invalid parameter:-SECURITY_DRIVER"

改成

grep -q -- ":-SECURITY_DRIVER"

即可

--fin--

谢太数据库

谢太的最爱

Blog Archive

Tuesday, August 30, 2011

bash quicksort

Wednesday, August 17, 2011

Getting MySQL process list with GDB

Tuesday, August 16, 2011

MySQL General Query Log

MySQL Proxy RW Splitting

ODI FAQ

Followers

谢太数据库

订阅谢太数据库

谢太的最爱

Blog Archive

Tuesday, August 30, 2011

bash quicksort

Wednesday, August 17, 2011

Getting MySQL process list with GDB

Tuesday, August 16, 2011

MySQL General Query Log

MySQL Proxy RW Splitting

ODI FAQ

Followers