mysql

org.springframework.dao.IncorrectResultSizeDataAccessException

今天在写一个游戏接口的时候,以为数据库中就一条记录,想当然的写下了

select appid, code, name, url from hall_games where code=:code

测试机上一运行就出现这个异常。
org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 0 or 1, actual 8: com.xiaonei.in.dao.LoginUserDAO#getHallGameInfo

于是查看文档。
Data access exception thrown when a result was not of the expected size, for example when expecting a single row but getting 0 or more than 1 rows。
当期望返回的结果记录是1时,如果返回值为0或者>1会抛此异常。于是修改sql,当需要一条数据的时候,还是老实加上limit 1比较好。

select appid, code, name, url from hall_games where code=:code limit 1

周五工作总结

每周记录下零星的收获吧,无论对心情还是以后的技术或多或少有点益处的吧,大部分摘自weibo/twitter
先上技术类:
1. discuz论坛装插件,/forumdata/目录要有可读可写的权限,今天被坑了好长时间,直接从线上复制下来的程序,到本地解开后属性丢了,最后才想起来权限的问题。
2. Nginx+fastcgi报错502/504,解决方案已经写这儿了
3. Mysql权限开通: grant all privileges on game_stat.* to webgame@’10.3.16.26′ identified by ‘webgame’;flush priviledges;
现在插入生活:
1.在家抓紧时间看书-学习,这个摆在第一位。
2.照顾好LP和小宝。
3.地铁路上无聊看网络小说,不过好多没结局的,以后提前搜下,没结局的就不看了

mysql bit field

在前面的文章中,我分享了目前在项目中使用的一个很好用的python脚本,《IP log analysize and record》,python脚本很简单,懂得python的朋友应该都很容易看懂,在本片文章中,重点讲述一下在mysql操作中的一点注意事项。
首先,我们来分析一个场景:用户访问一个站点的时候,我们把用户的访问行为记录了下来,这点无论是Apache还是Nginx都能很好得把access log记录下来,可能一个用户一天会访问好多次,但是我们的统计需求往往只会要求判断某个用户在一段时间内的登录情况,比如腾讯朋友空间的日活跃情况,细节只是定位到天,好了,我们的pv就变成了uv,但是uv的明细表依然很庞大,我们可以继续压缩,把每个用户每个月的访问情况,压缩成一条记录,这个时候,Mysql的一个特殊的字段类型 bit就出场了,一般情况下我们定义为 bit(31)即可,每个月一张表,用户的访问情况可以如下: More

work twitter-mysql insert or replace

When we need to insert some data into databases, we can utilise the basic “insert” sql, those we usually used in the past work,for example:

insert into someTable (colum1,colum2,..) values (val1, val2, ...);
insert into someTable values (val1, val2, val3 ...)

But in the development,we sometimes require some unique date in the database, in another word, when the sql insert some into the db, if find the same record has existed,then ignore it ,and continue to do the next query,

insert ignore into someTable (colum1,colum2,..) values (val1, val2, ...);
insert ignore into someTable values (val1, val2, val3 ...)

At the same time , some individuals assert that they may use the “replace” query to replace the “insert ignore”.
There are some attention that are alerted.
1.replace need the primary key or the unique index to update, thinking the below sql:

replace into tableA values(val1, val2, ...);
replace into tableA select val1, val2, ... form tableB
replact into tableA set colum1 = val1, colum2 = val2, ....., columN = concat(valN, "some constant");

Notice: if use the “concat” ,the updated data can not include NULL value, for instance, concat(“tom”, NULL, “david”), the result will be NULL, it can not be the “tomdavid”
2. if some want to update a special line, the next sql is good:

insert ignore into tableA (colum1, colum2, ..., columN) values (val1, val2, ..., valN); //Use ignore key word
update tableA set columN = columN | 1<< [$constant]           //calculate the new colum value 

Use crontab to finish works which will deal with access logs and load into infobright database

在昨天的文章中,我们讲述了如何使用Python来访问Java程序,在今天我们继续聊聊脚本的知识。最近由于业务的需要,给数据运维人员搞了个先进的数据查询引擎-Infobright,有关infobright的鼎鼎大名早已经是如雷贯耳,数据压缩上确实很牛叉,可以参考《Infobright数据仓库搭建》《Infobright的数据类型以及优化相关》。这几篇都跟mysql优化类似,但是略有不同还是注意的好,为了更好的优化效果,比如我在创建数据库表的时候就把原来的Varchar类型的字段创建成了Char,因为varchar的效率很低。
数据引擎搭建起来了,接下来我们需要自动的把数据加载到我们的数据库,由于众所周知的原因,目前社区办的ICE只支持load加载数据,所以我们得把我们的日志文件先处理成Infobright能够接受的类型,比如常见的csv格式。然后使用脚本自动的把我们的结果数据搞到数据库里面就可以了。
第一个脚本,完成日志目录的创建,日志合并,日志处理

#!/bin/sh

export LOG_DIR=/data1/remote-log-server/log/
today=`date "+%Y-%m-%d"`
yestoday=`date -d '1 days ago' "+%Y-%m-%d"`

GameLoginPath=$LOG_DIR/gamelogin/$today

#merge yesterday log
/bin/cat $LOG_DIR/gamelogin/$yestoday/gamelogin.log.* > $LOG_DIR/gamelogin/$yestoday/gamelogin.log
#use python script deal with access log to infobright data format
/usr/bin/python /data1/weige/python/gamelogin_weige.py $yestoday 99
#auto load data which can be accepted by infobright engine
/bin/sh /data1/remote-log-server/update_data_to_db.sh $yestoday

#create today log dir
if [ ! -d "$GameLoginPath" ]; then
        /bin/mkdir -p "$GameLoginPath"
fi

接下来的章节,我们将详细分拆解释我们这个脚本中间运行的这三个脚本,同时进一步对resin容器进行优化。

xtrabackup对数据库进行备份

一个大型的网站,数据的完整性往往是重中之重,因此,我们一般会使用传统的master-slave的架构来确保能有多份数据副本,但是,这种结构有其不 足之处,如被执行”delete”、”truncate”、”drop”,亦或是不可抗拒的原因造成的数据丢失情况,因此,这时就需要我们能够留下数据快 照,以便在发生这样情况的时候,尽可能的挽救我们的数据。

数据快照的方式多种多样,这里介绍一个现在正在使用的方式:使用xtrabackup(以下都以version=1.6为例)进行数据热备。

Xtrabackup是由percona开发的一个开源软件,此软件可以说是innodb热备工具ibbackup的一个 开源替代品。这个软件是由2个部分组成的:xtrabackup和innobackupex。Xtrabackup专门用于innodb引擎和 xtraDB引擎;而innobackupex是专门用于myisam和innodb引擎,及混合使用的引擎。如果你想使用源代码方式安装的话,则会发现其安装方式有点古怪,这是因为它采用的在MySQL源代码上打补丁构建的方式。

tar zxf xtrabackup-0.8.tar.gz
cd xtrabackup-0.8
./configure
make
##进行到这里时,千万别惯性使然接着make install,那样就会接着安装MySQL了,正确方法是接着:
cd innobase/xtrabackup/
make
make install

在备份数据的过程中,我们需要解决几个问题:

1、数据库服务器空间不足,就现有服务器来说,一般都是600G硬盘,往往数据文件就有300G+,因此,本机备份不可取,而且也不太能满足容灾的需要

2、备份的维护成本,尽量使备份能够可配置化。

为了解决上面2个问题,我们这样来操作:

先做下面几个假设,以便能够进行讲解:

1、假设我们需要备份的database在服务器IP:A,并且在A上已经安装xtrabackup

2、假设我们要将数据备份到服务器IP:B

在B上执行的脚本片段如下:

for .. in {…..};do

……

#在本地后台启动1234监听,以便接受备份文件

nc -d -l 1234 > /data/$A/20100118000001.tar &

#ssh到服务器A执行备份,并将备份包推送到备份机B上。同时记录下log。

ssh $A ‘innobackupex-1.5.1 –stream=tar –include=’renren.*’ –throttle=500 –socket=/data/mysql/backup/my
sql.sock –defaults-file=/data/dbbackup/my.cnf –user=root –password=xxxxxxx –slave-info /data/dbbackup | nc
$B 1234′ < /dev/null > /data/xtrabackup.log 2>&1

……

done;

从上面脚本片段我们能看出:我们使用了xtrabackup的备份脚本 innobackupex-1.5.1,对上面涉及到的参数进行解释:

–stream=tar : 告诉xtrabackup,备份需要以tar的文件流方式输出。

–include=’renren.*’:备份包含的库表,如例:意思是要备份renren库中所有的表。如果需要全备份,则省略这个参数;如果需 要备份renren库下的2个表:tableA & tableB,则写成:–include=’renren.tableA|renren.tableB’;再如果renren库下只有2个前缀是 table的表,你还可以写成:–include=’renren.table*’。

–throttle=500:xtrabackup在备份过程中,IO操作比较多,因此需要限定一下IO操作。以免服务器压力过大,不过好像作用不太明显,也不知道是不是设置的还是过大。待测…

–socket=/data/mysql/backup/mysql.sock:指定mysql.sock所在位置,以便备份进程登录mysql.

–defaults-file=/data/dbbackup/my.cnf :如果您的/etc/my.cnf中没有使用如[mysqld3306]的片段启动mysql,则不需要这行参数,如果使用了上面的片段,那么 xtrabackup是无法解析/etc/my.cnf文件的(严格上来说是会解析错误),所以,我们需要手工完成一个my.cnf文件,文件片段如下, 只需要指定6个参数即可让xtrabackup正常工作,各参数意思请参考mysql文档:

[mysqld] datadir=/data/var
innodb_data_home_dir = /data/ibdata
innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /data/iblogs
innodb_log_files_in_group = 2
innodb_log_file_size = 1G

–user=root –password=xxxxxxx :登录账号密码,root用户

–slave-info :告诉xtrabackup,最后备份文件中包含slave信息

xtrabackup可以在不加锁的情况下备份innodb数据表,不过此工具不能操作myisam。innobackupex-1.5.1是一个脚本封装,能同时处理innodb和myisam,但在处理myisam时需要加一个读锁。 更多的使用方法和注意事项,可以参考这篇文章

Infobright的数据类型以及优化相关

在前面的章节中,我们已经简单介绍了数据库仓库Infobright的安装和启动,数据导入导出的介绍,在本章节中我们着重介绍下Infobright的数据类型,尽管Infobright里面支持所有的MySQL原有的数据类型。但是Integer类型比其他数据类型更加高效,所以在建表的时候要尽可能使用以下的数据类型:

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT
DECIMAL(尽量减少小数点位数)
DATE ,TIME

效率比较低的、不推荐使用的数据类型有:
BINARY VARBINARY
FLOAT
DOUBLE
VARCHAR
TINYTEXT TEXT
针对这几个字段,在Infobright数据类型使用的时候,总结出一些经验和注意点:

(1)Infobright的数值类型的范围和MySQL有点不一样,比如Infobright的Int的最小值是-2147483647,而MySQl的Int最小值应该是-2147483648。其他的数值类型都存在这样的问题。

(2)能够使用小数据类型就使用小数据类型,比如能够使用SMALLINT就不适用INT,这一点上Infobright和MySQL保持一致。

(3)避免效率低的数据类型,像TEXT之类能不用就不用,像FLOAT尽量用DECIMAL代替,但是需要权衡毕竟DECIMAL会损失精度。

(4)尽量少用VARCHAR,在MySQL里面动态的Varchar性能就不强,所以尽量避免VARCHAR。如果适合的话可以选择把VARCHAR改成CHAR存储甚至专程INTEGER类型。VARCHAR的优势在于分配空间的长度可变,既然Infobright具有那么优秀的压缩性能,个人认为完全可以把VARCHAR转成CHAR。CHAR会具有更好的查询和压缩性能。

(5)能够使用INT的情况尽量使用INT,很多时候甚至可以把一些CHAR类型的数据往整型转化。比如搜索日志里面的客户永久id、客户id等等数据就可以用BIGINT存储而不用CHAR存储。其实把时间分割成year、month、day三列存储也是很好的选择。在我能见到的系统里面时间基本上是使用频率最高的字段,提高时间字段的查询性能显然是非常重要的。当然这个还是要根据系统的具体情况,做数据分析时有时候很需要MySQL的那些时间函数。

(6)varchar和char字段还可以使用comment lookup,comment lookup能够显著地提高压缩比率和查询性能。
Infobright执行查询语句的时候,大部分的时间都是花在优化阶段。Infobright优化器虽然已经很强大,但是编写查询语句的时候很多的细节问题还是需要程序员注意。这里涉及的内容比较多了,总结如下:

  • 尽量不适用or,可以采用in或者union取而代之。
  • 减少IO操作,原因是infobright里面数据是压缩的,解压缩的过程要消耗很多的时间。
  • 查询的时候尽量条件选择差异化更明显的语句
  • Select中尽量使用where中出现的字段。原因是Infobright按照列处理的,每一列都是单独处理的。所以避免使用where中未出现的字段可以得到较好的性能。所以select count(*) from table的速度要比select count(*) from table where condition的速度要快,同时查询数据时尽量使用 select count(game_code) from game_log where game_code=’qjp’,而不是 select count(uid) from game_log where game_code=’qjp’,前者使用同一列而后者要用两列计算,解压缩
  • 限制在结果中的表的数量,也就是限制select中出现表的数量。
  • 尽量使用独立的子查询和join操作代替非独立的子查询
  • 尽量不在where里面使用MySQL函数和类型转换符
  • 尽量避免会使用MySQL优化器的查询操作
  • 使用跨越Infobright表和MySQL表的查询操作
  • 尽量不在group by 里或者子查询里面使用数学操作,如sum(a*b)。
  • select里面尽量剔除不要的字段。尽可能少用select *
  • 有关varchar和char的区别,以及怎么选择,可以参考这篇文章

    MySQL Infobright-数据仓库笔记

    由于项目中的登录log一直比较大,目前是每周切分一张表,每次做月季度数据分析的时候就很痛苦,今天特定请教了一下公司DBA的同学,学到了两个解决方法。一个是把每天的登录按照位运算的形式保存,查询的时候使用bit_count(field)来计算,速度会大大提升,这个以后在项目中实践了再来分享;另一个就是使用Infobright引擎,Infobright是一个列存数据仓库软件,可以与MySQL集成,作为MySQL的一个存储引擎来使用。详细的框架结构可以参考官方的白皮书,国内也有介绍的,比如这一篇文章

    Infobright是一个与MySQL集成的开源数据仓库(Data Warehouse)软件,可作为MySQL的一个存储引擎来使用,SELECT查询与普通MySQL无区别。

    一、Infobright的基本特征:
    优点:
    查询性能高:百万、千万、亿级记录数条件下,同等的SELECT查询语句,速度比MyISAM、InnoDB等普通的MySQL存储引擎快5~60倍
    存储数据量大:TB级数据大小,几十亿条记录
    高压缩比:在我们的项目中为23:1,极大地节省了数据存储空间
    基于列存储:无需建索引,无需分区
    适合复杂的分析性SQL查询:SUM, COUNT, AVG, GROUP BY

    限制:
    不支持数据更新:社区版Infobright只能使用“LOAD DATA INFILE”的方式导入数据,不支持INSERT、UPDATE、DELETE
    不支持高并发:只能支持10多个并发查询。

    安装可以参考这篇文章 MySQL Infobright 数据仓库快速安装笔记[原创]。不过最新版已经没有64位的源码,我们可以直接下载rpm或者deb版本进行安装。

    1. Download the install package (e.g. infobright-3.4-x86_64.rpm) to the server where you are installing Infobright

    2. Obtain root user access

    3. To install the RPM package, run:

    rpm -ivh infobright_version_name.rpm [optional: --prefix=path]

    To install the DEB package, run:

    dpkg -i infobright_version_name.deb

    Note: Please do not install ICE in the root or home directories due to possible MySQL permission checking issues during install, start up, and/or load. [需要注意会有mysql的权限问题,所以安装的目录需要chown mysql:mysql授予访问权限]

    4. To change the default install options, after installation run:

    /usr/local/infobright/postconfig.sh

    You can run this script at any time after installation to change the datadir, CacheFolder, socket, and port. The script must be run as root and ICE must not be running. 【需要在infobright停止运行的时候再修改目录相关,该脚本需要在安装目录下运行,所以需要承 cd /usr/local/infobright/

    5. The installation determines the optimum memory settings based on the physical memory of the system. You may change these settings by editing the file brighthouse.ini within the data directory.

    Important: The memory settings assume that there are no other services on the machine consuming significant memory. If this is not the case, please lower the memory settings for Infobright.

    6. To start or stop ICE, run:

    /etc/init.d/mysqld-ib start
    /etc/init.d/mysqld-ib stop

    7. To connect to ICE, use the script mysql-ib:

    /usr/bin/mysql-ib [optional:db_name]

    8. To uninstall ICE, run either:

    rpm -e infobright
    dpkg -r infobright

    9. 示例:从普通的MySQL数据库(假设MySQL安装路径为/usr/local/webserver/mysql)导出数据到csv文件:

    /usr/local/webserver/mysql/bin/mysql  -S /tmp/mysql3306.sock -D tongji_logs -e "select * from  log_visits_2010_05_10 into outfile '/data0/test.csv' FIELDS TERMINATED  BY ',' ENCLOSED BY '"'  ESCAPED BY '\' LINES TERMINATED BY 'n';"

    10.示例:普通MySQL和Infobright建表对比

    ①、普通MySQL的InnoDB存储引擎建表:
    CREATE TABLE IF NOT EXISTS `log_visits_2010_05_12` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `cate_id` int(11) NOT NULL,
    `site_id` int(11) unsigned NOT NULL,
    `visitor_localtime` char(8) NOT NULL,
    `visitor_idcookie` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `cate_site_id` (`cate_id`,`site_id`),
    KEY `visitor_localtime` (`visitor_localtime`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    ②、Infobright的BRIGHTHOUSE存储引擎建表:
    CREATE TABLE IF NOT EXISTS `log_visits` (
    `id` int(11) NOT NULL,
    `cate_id` int(11) NOT NULL,
    `site_id` int(11) NOT NULL,
    `visitor_localtime` char(8) NOT NULL,
    `visitor_idcookie` varchar(255) NOT NULL,
    ) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;

    注:BRIGHTHOUSE存储引擎建表时不能有AUTO_INCREMENT自增、unsigned无符号、unique唯一、主键PRIMARY KEY、索引KEY。
    11、示例:从csv文件导入数据到Infobright数据仓库:

    /usr/local/infobright/bin/mysql  -S /tmp/mysql3307.sock -D dw --skip-column-names -e "LOAD DATA INFILE  '/data0/test.csv' INTO TABLE log_visits_2010_04_13 FIELDS TERMINATED BY  ',' ESCAPED BY '\' LINES TERMINATED BY 'n';"

    12、更改目录后不能启动mysqld的种种解答

    1.配置文件在/etc/my-ib.cnf ;2. 启动脚本/etc/init.d/mysqld-ib可以知道配置文件中设置了mysql的sock,必须得让mysql对mysql.sock存放路径有访问权限

    mysql使用小记optimize和analyze

    收录几个常用的命令,免得自己每次查找起来费劲。

    1. 优化表使用optimize
    语法 : optimize table 表名字
    另外,还可以使用mysqlcheck
    语法:

    # mysqlcheck -o 数据库名 表名 -uroot -p111111 (一张表)
    # mysqlcheck -o 数据库名 表名1 表名2 -uroot -p111111 (多张表)
    # mysqlcheck -o 数据库名 -uroot -p111111 (对整个数据库)

    最后,myisamchk一样可以用来完成类似的工作。语法会复杂一点,dba一般喜欢用这种的,或者习惯了linux命令操作的想必也会喜欢。基本语法如下:

    # myisamchk --quick --check-only-changed --sort-index --analyze 表名
    # myisamchk -r 表名 (参数-r表示对表进行修复,同时也删去了浪费的空间)
    # myisamchk -r /usr/local/mysql/data/testblog/article (指定表所在的路径)

    新手还是推荐使用optimize吧,简单容易操作。需要特别注意的是,optimize需要有足够的硬盘空间,否则可能会破坏表,导致不能操作,还得请optimize的师兄repaire来帮忙。

    2. 对数据库表进行分析

    同样有三种方法。
    连接到MySQL时,使用ANALYZE TABLE语句;
    利用mysqlcheck命令行工具(服务器需要运行,并且只对MyISAM表起作用);
    利用myisamchk命令行工具(服务器不应该运行,或无对所操作的表发生互操作)

    操作示例:

    # ANALYZE TABLE 表名;
    # mysqlcheck -a 数据库名 表名 -uroot -p111111
    # mysqlcheck -a 数据库名 表名1 表名2 -uroot -p111111
    如果试图对不支持分析操作的表进行分析(如InnoDB),那操作将无法进行
    # myisamchk -a /usr/local/mysql/data/数据库/表名

    analyze的结果如下:
    mysql> ANALYZE TABLE users;
    +———-+———+———-+———-+
    | Table | Op | Msg_type | Msg_text |
    +———-+———+———-+———-+
    | fb.users | analyze | status | OK |
    +———-+———+———-+———-+
    1 row in set (1.03 sec)

    更多的Mysql使用,可以参考mysql用户权限管理和mysql操作技巧,获取记录的ID和父ID.

    MYSQL创建用户和权限相关

    创建用户并授权
    GRANT语句的语法看上去像这样:
    GRANT privileges (columns)
    ON what
    TO user IDENTIFIED BY “password”
    WITH GRANT OPTION

    MySQL 赋予用户权限命令的简单格式可概括为:
    grant 权限 on 数据库对象 to 用户
    一、grant 普通数据用户,查询、插入、更新、删除 数据库中所有表数据的权利。

    grant select on testdb.* to common_user@'%'
    grant insert on testdb.* to common_user@'%'
    grant update on testdb.* to common_user@'%'
    grant delete on testdb.* to common_user@'%'

    或者,用一条 MySQL 命令来替代:

    grant select, insert, update, delete on testdb.* to common_user@'%'

    二、grant 数据库开发人员,创建表、索引、视图、存储过程、函数等权限。
    More