MYSQL 遭遇 The total number of locks exceeds the lock table size

今天进行MySql 一个表数据的清理，经过漫长等待后出现 The total number of locks exceeds the lock table size 提示。以为是table_cache的值设置小了，于是将其值修改变大, 重启 MySQL 服务，再次执行表的清理操作，经过漫长时间等待后，同样的问题再次出现。网上google搜索相关问题，发现这么一段：

If you’re working with a large InnoDB table and you’re updating, inserting, or deleting a large volume of rows, you may stumble upon this error:

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

InnoDB stores its lock tables in the main buffer pool. This means that the number of locks you can have at the same time is limited by the innodb_buffer_pool_size variable that was set when MySQL was started. By default, MySQL leaves this at 8MB, which is pretty useless if you’re doing anything with InnoDB on your server.

原来是InnoDB表执行大批量数据的更新，插入，删除操作时会出现这个问题，需要调整InnoDB全局的innodb_buffer_pool_size的值来解决这个问题。
于是对照者检查了一下我的表类型，发现果然是InnoDB类型的，于是按照提示，修改 innodb_buffer_pool_size 的值，再一次重启mysql服务器，执行表清理操作，经过漫长时间等待后，终于成功执行完毕。

有无办法不用重启MySQL服务器做清理呢？
按照那篇文章介绍，可以将清理的操作分为几段，每次清理一部分（比如清理5-10%的数据），使每次的清理不会超出lock table size 的限制，这样就可以在不重新启动MySQL的情况下完成数据清理。

Popularity: 8% [?]

Related

Tags：.
November 25, 2010 | Filed Under MySQL | Comments Off 

使用MySQL Connector C++的PreparedStatement更新数据碰到类型异常

使用MySQL Connector C++ 的preparedStatement，在更新数据时，碰到异常

  1.  pstmt = con->prepareStatement("INSERT INTO logs(day, time, uid, vid, cid, cv, did, res) VALUES (?,?, ?,?, ?,?, ?,?)");
  2.  pstmt->setInt(1, day);
  3.  pstmt->setInt(2, time);
  4.             //…..
  5.         pstmt->setInt(8, res);
  6.  
  7.  pstmt->executeUpdate();

执行executeUpdate时碰到异常: Using unsupported buffer type: 0 (parameter: 1)

不知道怎么回事，使用google搜索了一会，没有找到解决方法。
没有办法，将相关代码替换使用Statement的execute方法处理，执行ok

Popularity: 7% [?]

Related

Tags：.
May 19, 2010 | Filed Under C/C++, MySQL | 2 Comments 

源码安装Mysql提示No curses/termcap library found

使用源码方式安装Mysql5.0,提示No curses/termcap library found
google了一下，
下载一个ncurses-5.6.tar.gz安装，

  1. wget http://ftp.gnu.org/pub/gnu/ncurses/ncurses-5.6.tar.gz
  2. tar zxvf ncurses-5.6.tar.gz
  3. cd ncurses-5.6
  4. ./configure –prefix=/usr –with-shared –without-debug
  5. make
  6. make install clean

网上还有一篇说在./configure 时添加 –with-named-curses-libs=/usr/lib/libncurses.so.5 解决，但是添加后虽然./configure通过，但是在make时失败
在这儿记录一下

Popularity: 7% [?]

Related

Tags：.
January 22, 2010 | Filed Under MySQL | Comments Off 

在Shell中执行Mysql语句

使用shell脚本做日志分析，为了将分析后的结果直接存储到mysql数据库中需要在shell脚本中执行mysql的语句。
方法一 将每一步需要执行的语句保存到 tmp.sql 中，最后在使用

  1.   mysql -u uname -p pwd < tmp.sql

方式执行

方法二 使用参数传递执行
mysql -u$user -p$pass -D $db -e “select afield from atable;”
例: 对test.dat中的每一行拼装成sql做执行

  1. cat test.dat |awk '{print "insert into table(a, b) values(\047" $1 "\047, \047" $2 "\047)" }'|while read line
  2. do
  3.   mysql -u$user -p$pass -D $db -e $line
  4. done

其中 awk print命令中的 \047 代表单引号

Popularity: 7% [?]

Related

Tags：.
December 15, 2009 | Filed Under MySQL | 1 Comment 

在php中使用mysql存储过程以及碰到的问题解决方法

mysql 5中增加了存储过程功能，他们在php中使用很方便。
在php中调用存储过程和函数的主要步骤为。

一.准备存储过程的参数

如果存储过程有输入参数，则需要声明一个mysql变量，让mysql服务器知道此变量的存在，执行mysql语句 mysql_query(“set @mysqlvar =$phpvar”);
就可以在mysql服务器里面就有一个变量@mysqlvar。如果时IN参数，那么其值可以有phpvar传入。

二.调用存储过程

1.执行 call procedure（）语句
也就是mysql_query(“call proceduer([var1]…)”);

2. 如果参数有OUT返回值，执行select @var，获取返回的参数结果
mysql_query(“select @var”)

接下来的操作就和php执行一般的mysql语句一样了。可以通过mydql_fetch_row()等函数获得结果。

如果是函数。 直接执行 select function（） 就可以了。

———使用中碰到的问题解决——-

不能执行存储过程

在定义存储过程的时候，将存储过程类型设置为 DETERMINISTIC 。

定义的存储过程中有多个select语句，call失败

通过 mysql_error()输出错误提示 can’t return a result set in the given context
在mysql_connect连接数据库时指定 CLIENT_MULTI_RESULTS 标志。

  1. <?php
  2.     define(’CLIENT_MULTI_RESULTS’, 131072);
  3.  
  4.     $conn = mysql_connect("localhost", "root", "rootpwd",1,CLIENT_MULTI_RESULTS) or die("Could not connect: ".mysql_error($conn));
  5.     mysql_select_db("mydb", $conn) or die("Could not select database");
  6.    
  7.     $result = mysql_query("call proc_get_datas(2)", $conn);
  8.  
  9.     while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  10.          // do something
  11.     }
  12.  
  13.      mysql_free_result($result, $conn);
  14.    
  15.     mysql_close($conn);    
  16. ?>

Popularity: 6% [?]

Related

Tags：, .
August 14, 2009 | Filed Under PHP, Programming | Comments Off 

MySQL中Group_Concat函数妙用

Group_Concat 是 MySQL 中用户Group By 的一个函数，函数语法如下:

  1. GROUP_CONCAT([DISTINCT] expr [,expr …]
  2.     [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col …]]
  3.     [SEPARATOR str_val])

这个函数在 MySQL 4.1 中被加入。函数返回一个字符串结果，该结果由分组中的值连接组合而成。

充分利用此函数，可以简化我们程序中的一些写法。
比如我们现在有一个学生成绩表，为了方便不同班级科目的设置，对学生的成绩没有采用一条记录多个字段的方式，而是采用多条记录一个成绩字段方式存储。

  1.   CREATE TABLE stu_grade(
  2.     `id` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
  3.     stuname varchar(20), #学生姓名
  4.     course   varchar(20), #科目名称
  5.     score    int, #本科目成绩
  6.     KEY `id` (`id`)
  7.   ) TYPE=MyISAM;

现在要求获得一个班级中学生各科成绩列表。
通过Group_Concat 函数，我们只要简单执行如下的SQL语句，

  1.   SELECT `stuname`,
  2.       GROUP_CONCAT(concat(`course`, ':', score)
  3.                    ORDER BY `course` DESC SEPARATOR ",") AS Result
  4.       FROM `stu_grade`
  5.       GROUP BY `stuname`;

然后在程序中通过一个循环，将选出的结果输出来就可以了。如果不使用，我们通常需要将选出来的数据做再次整理，(多增加一些变量做一些循环判断，没有采用Group_Concat后的逻辑简单)

Popularity: 5% [?]

Related

Tags：.
January 6, 2009 | Filed Under MySQL | Comments Off 

MySQL主从服务器的一些技巧(转)

原作者: 老王
原文地址

红色字为sunnyu加的批注
———–

问题：主从服务器表类型的选择
一般的共识是主服务器使用innodb，事务，行锁等功能是myisam所没有的，对修改操作而言，它更高效；从服务器使用myisam，全文检索功能是innodb所没有的，对查询操作而言，它更高效。这样就可以各尽其能。
呵呵，主从库各司其职，主库：最快的速度做添加删除修改操作，从库，最快的速度做查询操作

问题：主从服务器字段类型的选择
字段类型对于分页等操作有很大影响。主服务器一般是innodb，因为不涉及查询，所以可以使用varchar等来存储字符串来节省空间，从服务器一般是 myisam，因为涉及查询，所以必须在char和varchar之间仔细权衡，没有varchar, text, blob字段的表是静态表，反之是动态表，静态表的检索效率要比动态表好若干倍，一般来说，所有涉及大结果集的查询都应该尽可能保证在静态表上完成，这里 说一个例子：比如说常见的articles表有title(varchar), body(text)等字段，在做文章列表的时候，因为不是静态表，所以查询不会很快，下面开始重构解决方案：把原来的articles表拆分成 subjects表和contents表，title字段设置为一个足够的char类型放在subjects表里，body字段还保持是text类型放到 contents表里，subjects和contents表之间的关系是一对多，这样，顺带着也方便的实现了多页文章的功能，而且更重要的是在查询文章 列表的时候，操作都是在subjects静态表里完成，效率肯定会比前一种方案提升很多。

强调：MyISAM里静态表和动态表的区别对性能影响极大，但我敢说很大一部分使用者并没有注意过这一点！如果你就是其中之一，那么我强烈建议你再次体会 一下前面说的articles分解为subjects/contents的过程，相信你熟悉了以后，下一个应用的速度会有质的提升。
唉，我就是那不知道的当中一人，受教了

问题：主从服务器NOW()函数造成数据不一致
假设在主服务器上执行一条INSERT …. VALUES ( …, NOW())，那么在从服务器上也会同样执行一条的SQL语句，但是主从服务器各自的时间设置可能不一致（比如说时区不同），NOW()在两台服务器上的 结果就可能不一致。在MySQL5.1里，将支持行复制，那时候就不存在这个问题了。不过不管怎么说，都不应该在程序里使用NOW()，时间的计算在应用 程序里完成。这里介绍一个额外的小技巧：获得时间戳，和time()相比，$_SERVER[‘REQUEST_TIME’] 少做了一次系统调用，不过是否合适要视客观情况而定。
这点不敢苟同，binlog上有时间戳信息，所以应该可以放心大胆的使用NOW函数，使用$_SERVER[‘REQUEST_TIME’]并不能得到实际操作数据库的时间

问题：主从服务器读写分离时读操作失败
先重现一下问题：比如说添加一条新数据，添加成功后根据last_insert_id跳转到新添加数据的浏览页面。在此过程中添加新数据的操作是在主服务 器上完成的，浏览新数据的操作实在从服务器上完成的，不过由于主从服务器间SQL同步存在延迟，所以当使用last_insert_id在从服务器上查询 的时候，从服务器很可能还没有还没来得及同步到此记录，所以读操作失败。解决思路也不复杂，在代码里加入一个缓存层（可以使用memcached），新添 加的数据都顺手放到缓存层里一份，新数据的读操作也先查询缓存层，这样就不会再有读操作失败的问题了，当然删除或者更新数据的时候也要顺带着处理好缓存数 据，可以使用观察者模式来搞定。不过这样缓存方案只限于读取单一的记录，对于读取列表的记录的情况，则是无效的。
也可以直接从主库获取数据啊，毕竟这种操作量是少的，而且是根据主键来的，稍微牺牲一下主库应该关系不大

问题：主从服务器索引是否有必要保持一致
一般都是利用主从服务器完成读写分离，从服务器上进行读操作，主服务器进行写操作，这样的话，主服务器上仅保留主键，外键，唯一索引等必要的索引即可，以 便保持数据合法性，而对于那些原本用于优化SELECT操作的索引，可以全部删除，如此的话主服务器的写操作效率会提升很多。
主库上保留的索引还应该考虑实际逻辑中相关删除修改操作的sql，要不然盲目删除一些索引可能会造成性能的直线下降，当然如果删除修改操作的条件都只是针对主键等的，那没有问题。
———–

Popularity: 5% [?]

Related

Tags：.
December 5, 2008 | Filed Under MySQL | Comments Off 

随机读取数据库记录的方法

为了一些抽奖等目的，有时候需要随机的从一些符合的条件的数据中获取几条做为中奖的记录。
这儿列出了一些常见数据库中用来获取随机记录数据的方法。

http://www.carlj.ca/2007/12/16/selecting-random-records-with-sql/

Popularity: 5% [?]

Related

Tags：.
November 1, 2008 | Filed Under MySQL | Comments Off 

MySQL定时增量备份的恢复处理脚本(mysqlbinlog)

在通过 MySQL定时增量备份处理脚本备份了数据后，我们还需要做恢复操作的脚本。
恢复起来相对简单，找到最近一天的数据或指定某天的数据，然后使用 mysqllogbin 根据日志做恢复处理。

步骤1：复制基准的全量数据

从备份目录下将最近备份的全量数据复制到工作目录下

  1. #!/bin/bash
  2.  
  3. PATH=/usr/local/sbin:/usr/bin:/bin
  4.  
  5. #get cur date str 2008-10-12
  6. DATE=`date -I`
  7. TIME=' 01:0:0'
  8. DAYSTARTTIME=" $DATE $TIME"
  9.  
  10. #set backup path info
  11. DATADIR="/var/lib/mysql"
  12. BAK_DIR="/backup/$DATE"
  13.  
  14. #set mysql user name and password
  15. MYSQL_USER="root"
  16. MYSQL_PWD="rootpwd"
  17. MYACC=" -u'$MYSQL_USER' -p'$MYSQL_PWD' "
  18.  
  19. cp -r $BAK_DIR $DATA_DIR

步骤2：根据日志文件对基准数据做恢复

对使用使用mysqlbinlog做数据的恢复。

  1.   HOST=`hostname -s`
  2.  
  3.   for binfile in `ls $DATA_DIR/$HOST-bin.0* |sort`; do
  4.      CMD="mysqlbinlog –start-datetime='$DAYSTARTTIME' $binfile  | mysql $MYACC "
  5.      eval $CMD
  6.   done

完整恢复脚本:

  1. #!/bin/bash
  2.  
  3. PATH=/usr/local/sbin:/usr/bin:/bin
  4.  
  5. #get cur date str 2008-10-12
  6. DATE=`date -I`
  7. TIME=' 01:0:0'
  8. DAYSTARTTIME=" $DATE $TIME"
  9.  
  10. #set backup path info
  11. DATADIR="/var/lib/mysql"
  12. BAK_DIR="/backup/$DATE"
  13.  
  14. #set mysql user name and password
  15. MYSQL_USER="root"
  16. MYSQL_PWD="rootpwd"
  17. MYACC=" -u'$MYSQL_USER' -p'$MYSQL_PWD' "
  18.  
  19. cp -r $BAK_DIR $DATA_DIR
  20.  
  21. HOST=`hostname -s`
  22.  
  23. for binfile in `ls $DATA_DIR/$HOST-bin.0* |sort`; do
  24.    RESTORECMD="mysqlbinlog –start-datetime='$DAYSTARTTIME' $binfile  | mysql $MYACC "
  25.    eval $RESTORECMD
  26. done

Popularity: 4% [?]

Related

Tags：, .
October 21, 2008 | Filed Under MySQL | Comments Off 

MySQL定时增量备份处理脚本

在提出 MySQL主从结构灾难恢复策略机制设想 后尝试编写用于以上目的的备份脚本。

增量日志备份

每日一个全量备份，同时做binlog 的删减(只保留一个小时的日志，根据需要可以做延时)，然后每小时对日志文件做增量部分的备份。

  1. #!/bin/bash
  2.  
  3. PATH=/usr/local/sbin:/usr/bin:/bin
  4.  
  5. #get cur date str 2008-10-12
  6. DATE=`date -I`
  7.  
  8. #set backup path info
  9. DATADIR="/var/lib/mysql"
  10. BASE_BAK_DIR="/backup"
  11. CUR_BAK_DIR="$BASE_BAK_DIR/current"
  12.  
  13. #set mysql user name and password
  14. MYSQL_USER="root"
  15. MYSQL_PWD="rootpwd"
  16. MYACC=" -u'$MYSQL_USER' -p'$MYSQL_PWD' "
  17.  
  18. #set backup type info
  19. INTERVAL="$1"
  20.  
  21. # days, files in base_bak_path
  22. # before this days will be deleted
  23. RETENTION=21
  24. #get host name (a part of bin log file names,
  25. #  if change set master bin file manual, need change this)
  26. HOST=`hostname -s`
  27.  
  28. #set mysql version
  29. MYVERSION="4.1"
  30.  
  31. if [ "$MYVERSION" = '4.1' ] || [ "$MYVERSION" = '5.0' ] ; then
  32.   #purge master logs to latest backup time (remain one hour bin log for slave replication delay)
  33.   PURGELOGS="mysql $MYACC -e \"PURGE MASTER LOGS BEFORE DATE_SUB( NOW(), INTERVAL 1 HOUR )\""
  34. else    
  35. echo "not support MYSql Ver $MYVERSION"
  36. exit 1
  37. fi
  38.        
  39. #check param input (daily or hourly)
  40. if [ ! $1 ];
  41. then    
  42.   read -p "Backup Interval? (Hourly|Daily) : " INTERVAL
  43. fi    
  44.  
  45. case $INTERVAL in
  46.   hourly | HOURLY | Hourly | H | h | 1 )
  47.   echo "Performing hourly level backup — `date`"
  48.   MYCMD="mysql $MYACC -e \"FLUSH LOGS\""
  49.   eval $MYCMD
  50.  
  51.   if [ -d $BASE_BAK_DIR/$DATE ] && [ "$MYVERSION" = '4.1' -o "$MYVERSION" = '5.0' ] ; then
  52.      rsync -aub $DATADIR/$HOST-bin.?????? $BASE_BAK_DIR/$DATE
  53.   else    
  54.      echo "dest dir not exists! please run daily backup first." 1>&2
  55.      exit 1
  56.   fi
  57.   exit 0
  58.   ;;
  59.  
  60.   daily | DAILY | Daily | D | d | 2 )
  61.   echo "Performing daily level backup — `date`"
  62.   # check dest path
  63.   if [ ! -d $CUR_BAK_DIR ]; then    
  64.      echo "Creating $CUR_BAK_DIR"
  65.      mkdir -p $CUR_BAK_DIR
  66.   fi
  67.  
  68.   MYCMD="mysqlhotcopy $MYACC –regexp=.* $CUR_BAK_DIR"
  69.   eval $MYCMD
  70.   chown -R mysql: $CUR_BAK_DIR/
  71.   mv $CUR_BAK_DIR $BASE_BAK_DIR/$DATE
  72.   eval $PURGELOGS
  73.  
  74.   #delete files that outdate
  75.   find $BASE_BAK_DIR -ctime +$RETENTION -exec rm -rf '{}' \;
  76.   exit 0
  77.   ;;
  78.  
  79.   * )
  80.   echo "Invalid Selection" 1>&2
  81.   exit 1
  82. esac

可以在脚本的前面指定好备份目录参数

DATADIR="/var/lib/mysql"
BASE_BAK_DIR="/backup"
CUR_BAK_DIR="$BASE_BAK_DIR/current"

以及用来连接数据库的账号的参数

#set mysql user name and password
MYSQL_USER="root"
MYSQL_PWD="rootpwd"

通过参数 RETENTION=21 来指定备份文件保留的天数，要视具体用来做备份的磁盘大小和数据库大小做综合衡量处理。

最后将脚本添加到crontab中定时运行
首先设定每天备份一次的，然后再设定每小时的备份，其中第一次的每小时备份应该是在每天备份执行之后。
数据库发生失败时的恢复在每日全量备份基础上通过日志文件快速恢复到最近时间的变化。

Popularity: 5% [?]

Related

Tags：, .
October 20, 2008 | Filed Under MySQL | 2 Comments 

Next Page →