一种批量插入数据saveIgnore并返回主键的方法

在mysql中,提供了save ignore语法,用于在插入数据如果出现冲突时忽略信息的处理方式.在这种情况下,一般主键id是通过自动生成,ignore通过一些惟一索引进行控制.在程序中的期望即如果惟一索引不冲突就插入新的数据,如果冲突则不再插入. 但都希望能够在处理成功之后拿到这些数据的主键(不管是之前的还是新插入的),以便于后续进行处理.相应的简单业务逻辑如下所示.

        List<T> personList = xxxList;
        mysql.saveIgnore(personList);
        
        //进行后续逻辑,如转账
        personList.forEach(t-> {
            mysql.addMoney(t.getId(), 100);
        });

在标准的jdbc中,如果是save,或者是mysql的save values(value1) (value2) 这种语法,是能够通过 statement.getGeneratedKeys()返回自动生成的主键.但对于save ignore无效,mysql并不是返回由于冲突处理的之前的主键信息.

常规的作法就是在save ignore之后,再通过相应的惟一索引来进行查询. 类似如下的sql方式

select id from t where t.name in(name1,name2);

这种方式,对于如果惟一索引仅有一列是没有问题的.但如果是多列组成的,则生成如下的sql

select id from t where (t.name,t.code) in ((name1,code1),(name2,code2));

理论上,这种方式也没有问题,但是在mysql中,这种语法并不能命中相应的索引,会造成全局扫描(验证版本5.5,5.6)
修改in版本为如下sql

select id from t where (t.name = name1 and t.code = code1) or (t.name = name2 and t.code = code2);

这种方式可以命中索引,但是整个sql会变得很长,可以看出,相应的惟一索引列会出现多次.如果是5000个数据处理(批量处理肯定数据会很多才有意义),则整个sql会很长.

本文的方式是通过临时表,提前插入索引数据,再通过表关联来获取相应的数据.这样可以避免上面sql过长的问题,并且利用mysql内存表快速处理数据.整个原理可以理解为以下几个步骤

  1. 创建临时表,表数据仅为惟一索引要求列,并建立相应的惟一索引
  2. 插入待处理数据中指定属性到临时表当中
  3. 使用save ignore语法插入数据到实际表中
  4. 两表关联,使用临时表关联实际表,指定相应的惟一列进行关联条件,查询出主键信息
  5. 程序中将主键处理到相应的对象中

继续阅读“一种批量插入数据saveIgnore并返回主键的方法”

mysql中错误码1406 data too long在jdbc和命令行的不同表现及处理

今天在同事开发过程中碰到一个mysql的错误码:1406,显示的错误信息为 data too long for column,而导致相应的事务被回滚.将此错误码(google 关键字 mysql 1406),即搜索到相应的错误信息,即相应的插入数据比实际的列的大小更大.然后,报相应的错误信息,最终处理失败.

但是,对于这条sql语句,如果将语句贴到命令行去执行,即得到另外一个结果.sql不会报错,但再次查询相应的数据信息,会发现插入的数据被自动截断了.在这种情况下,实际上插入的数据是错误的,即因为不同的行为得到不同的结果.

然后,再次google(关键字 mysql自动截断)会发现这两种情况是因为使用了不同的sql_mode导致.获取相应的sql_mode命令可以如下所示(笔者mysql为mariadb 10.1)

select @@sql_mode;

获取的数据信息为:NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION.

这种情况下,即会产生自动截断的设置.可以通过set @@sql_mode="xxx"来修改.影响截断地设置为 strict_trans_tables 或者 strict_all_tables.将其设置为其中之一即可.

反之,使用默认的jdbc连接mysql时,同样通过命令获取相应的数据,结果为
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
会发现相应的设置并不与命令行相同.

这里的原因在于,在mysql的jdbc驱动中,之前也是与命令行相同,但为了保证与jdbc规范相符(具体的规范没找到),引入了配置变量 jdbcCompliantTruncation.此变量可以在连接字符串中,追加并设置为false.其默认值为true.
在具体的mysql jdbc实现中(当前版本为5.1.39),此变量会在类ConnectionImpl中判定.如果为true,并且版本大于5.0.2,则会发语句set sql_mode=xxx 来进行重新设置.其重新设置的值,即在之前提到值的基础之上,追加值 STRICT_TRANS_TABLES. 然后,在此设置的影响下,插入数据超长即会直接报错,而不是自动截断.

继续阅读“mysql中错误码1406 data too long在jdbc和命令行的不同表现及处理”

haproxy和keepalived的个人理解

附参考文:http://weizhifeng.net/HA-with-HAProxy-and-KeepAlived.html

关于此两者,随便google一下都会有大量的文章,因此这里也不用介绍如何来进行安装和配置了。主要从一个整体方案的角度来描述什么要这样做,以及这样做所解决的问题。

所有的系统,都是先经历一个单台机器搞所有业务的时代,一个程序+一个mysql数据库,就可以满足开发及第一个版本上线的要求。随着,数据的增加以及业务的增长,这些应用就面临一个访问量的扩大以及扩展的问题。最简单的扩展就是水平扩展,原来由一个mysql增加为2个或多个,形成一个集群,这样最简单的能力就是提供更强的服务能力。如原来的访问量支持每秒1000,现在可以支持2000(理想值),相当于将服务能力分散到多个节点。
这里面涉及到多个问题,首先就是数据的相互备份,然后就是如何分配计算能力,外部如何来访问等。本文引入HaProxy和KeepAlived主要处理的就是一个外部访问问题。

在后面的介绍当中,假定有2个mysql,分别为mysqlA和mysqlB.

继续阅读“haproxy和keepalived的个人理解”

使用BasicDataSource引发的数据库连接中断的问题和解决方法

最近碰到一个问题,应用程序每天的第一次进行系统访问时,会报一个奇怪的错误,最后经过仔细的跟踪,错误信息找到了,如下所示:

 The last packet successfully received from the server was 60,428,178 milliseconds ago.  The last packet sent successfully to the server was 60,428,180 milliseconds ago

很明显,这个错误信息表示数据库连接实际上已经断开了,因为在深夜没有客户来访问系统。但是程序中并没有检测到这个信息,仍然使用过期的数据库连接,那肯定会报错。而在之后,datasource检测到这个问题,则尝试重新进行连接,这样接下来的链接就正常了。
在项目中使用的数据库连接池是dbcp,即apache-dbcp的BasicDataSource。最初的配置为:

<bean id="dataSourceLog" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="" />
		<property name="defaultAutoCommit" value="" />
		<property name="url" value="" />
		<property name="username" value="" />
		<property name="password" value="" />
		<property name="maxActive" value=""/>
</bean>

经仔细分析,这样的配置不会对连接的连接超时以及有效性进行检测,因此会发生以上的问题。要解决这个问题,就需要额外的检测手段和处理方法,还好,dbcp本身提供了这些配置,只不过当前系统中并没有这样的配置。

在处理这个问题中,需要处理好以下这些问题

  • 连接多少时间的数据库连接被认为是无效的
  • 如何检测一个连接是有效的
  • 如何检测那些执行SQL时间长的连接
  • 周期性连接检测需要多长时间执行一次

上面的这些问题,dbcp都提供了有效的配置方法,只需要详细的配置即可。如详细的配置如下:

//是否要进行检测
		<property name="testWhileIdle" value="true"/>
//进行检测一个连接是有效的SQL语句,比如oracle是select 1 from dual 而 mysql是 select 1
		<property name="validationQuery" value=""/>
//是否在数据库连接请求量大的时候,如总数50,当前已请求了49个,所剩不多了,检测那些执行时间久的连接,将其从池中移除掉(移除之后怎么作,由实现决定,如直接断开,或者任其继续执行等)
		<property name="removeAbandoned" value="true"/>
//一次数据库操作执行时间超过多少秒的连接被认为是需要移除的
		<property name="removeAbandonedTimeout" value=""/>
//每隔多少时间检测一次,比如每半小时检测一次,总不能总是检测,这会对性能产生影响
		<property name="timeBetweenEvictionRunsMillis" value=""/>
//每次检测时,需要检测多少个数据连接,一般设置为与最大连接数一样,这样就可以检测完所有的连接
		<property name="numTestsPerEvictionRun" value=""/>
//一个数据库连接连接多少时间之外,我们认为其应该不再适用了(可能下一次就会失效了),应该移除并重新建立连接了
		<property name="minEvictableIdleTimeMillis" value=""/>

经过这样配置之后,之前的问题就不再出现了。引起这个问题的原因是多方面的,但对于问题主因,仍是对dbcp未完全了解,总是按默认配置进行开发。在某些场景下就会突发一些想不到的问题。

在fedora 15中使用源码自定义安装路径安装mysql5.5.12

    近段时间安装了最新的fedora15,将硬盘重新划分了个data区,用于安装数据库软件。由于默认使用fedora中的yum去安装,只能安装在默认的usr/lib和usr/share数据,而不能自定义安装路径。所以尝试从源代码进行安装,以指定安装目录进行安装。

    从mysql中下载了最新的社区版mysql-5.5.12.tar.gz,解压之,开始安装。
    如果以前安装过mysql server,则需要删除以前删除的,并删除/usr/lib/mysql目录,/usr/share/mysql目录,避免重新安装的mysql跟以前相冲突。

    首先要下载几个用于安装之前所必需要的包:

cmake	用于进行项目配置,即相当于以前的auto_tools
ncurses-devel
bison
gcc-c++	用于源码编译

    使用cmake进行项目预配置:(在copy时请先copy至一个文本中,将其中的换行符去掉)

cmake -DCMAKE_INSTALL_PREFIX=/data/mysql -DDEFAULT_CHARSET=UTF8 -DDEFAULT_COLLATION=utf8_general_ci 
-DEXTRA_CHARSETS=all 
-DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DMYSQLD_USER=mysql -DWITH_DEBUG=0 
-DWITH_INNOBASE_STORAGE_ENGINE=1 .

    其中 -D表示往cmake传递相应的参数。
    CMAKE_INSTALL_PREFIX    安装路径,相当于以前的prefix
    DEFAULT_CHARSET    默认数据库语言
    DEFAULT_COLLATION    默认的数据库字符集(这个不指定,后面在安装数据库时会报错误)
    EXTRA_CHARSETS    支持其它语言
    MYSQL_TCP_PORT    服务启动端口
    MYSQL_UNIX_ADDR    端口锁定文件
    MYSQLD_USER    启动mysql的用户
    WITH_DEBUG    不使用调试模式
    WITH_INNOBASE_STORAGE_ENGINE    支持innodb数据库

    然后就是make 和make install了,后面的直接引用自官网。

cd /data/mysql	#进入到
chown -R mysql .	#改变当前目录拥有者
chgrp -R mysql .	#改变当前目录组
scripts/mysql_install_db --user=mysql	安装
chown -R root .	#重新改变当前目录拥有者
chown -R mysql data		#将data目录,即数据存放区修改为mysql拥有
#可选操作
cp support-files/my-medium.cnf /etc/my.cnf	#使用一个medium的配置文件替换默认的配置文件
bin/mysqld_safe --user=mysql &	#启动mysql