看trx_mysql_thread_id字段，这个是死锁的进程id，然后到主机那里kill掉即可。

经验提示：最好不要用客户端界面修改表结构，有可能会锁住。最好使用语句。（不过，其实我也经常贪图方便，直接用SQLyog直接修改表结构，方便嘛。偶尔锁住了kill掉即可，毕竟只是开发环境，生产环境必须不能如此）
增加字段：ALTER TABLE tf_b_depart ADD (PARENT_MAJOR VARCHAR(6));
修改字段：alter table tf_f_task_target modify VALUE decimal(16,2);
建表：CREATE TABLE td_s_salary_index (index_id VARCHAR(8));

mysql连接数不够

陆续开发人员的eclipse里突然报错信息：

MySQLNonTransientConnectionException
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: “Too many connections”
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

原因：max_connections mysql 默认值为100，超过了就会报错。重启mysql即可。为了防止后面再出现，需要把max_connections改为1000或更多。

<code class=" hljs makefile">[mysql@paas03 ~]$more my.cnf
[mysqld_multi]
<span class="hljs-constant">mysqld</span> = /app/mysql/bin/mysqld_safe
<span class="hljs-constant">mysqladmin</span> = /app/mysql/bin/mysqladmin
<span class="hljs-constant">user</span> = mysql
<span class="hljs-constant">password</span> = a@Aug22
<span class="hljs-constant">log</span>=/app/log/mysqld_multi.log
[mysqld01]
<span class="hljs-constant">port</span> = 3010
<span class="hljs-constant">socket</span> = /tmp/mysql.sock01
pid-file = /app/data_paas/db-app.pid
<span class="hljs-constant">basedir</span> = /app/mysql
<span class="hljs-constant">datadir</span> = /app/data_paas
<span class="hljs-constant">user</span> = mysql
symbolic-links=0
character-set-server=utf8
<span class="hljs-constant">lower_case_table_names</span>=1
<span class="hljs-constant">innodb_log_file_size</span>=128M
<span class="hljs-constant">innodb_log_buffer_size</span>=4M
<span class="hljs-constant">innodb_buffer_pool_size</span>=1G
<span class="hljs-constant">event_scheduler</span>=1
explicit_defaults_for_timestamp
<span class="hljs-constant">max_connections</span>=1500
<span class="hljs-constant">join_buffer_size</span> = 128M
<span class="hljs-constant">sort_buffer_size</span> = 10M
<span class="hljs-constant">read_rnd_buffer_size</span> = 2M</code>

参考：http://www.cnblogs.com/S-E-P/archive/2011/04/29/2045050.html

mysql的root密码修改

<code class="language-sql hljs "><span class="hljs-operator"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> mysql.<span class="hljs-keyword">user</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">USER</span>=<span class="hljs-string">‘root‘</span>;</span>
<span class="hljs-operator"><span class="hljs-keyword">SET</span> PASSWORD <span class="hljs-keyword">FOR</span> <span class="hljs-string">‘root‘</span>@<span class="hljs-string">‘localhost‘</span> = PASSWORD(<span class="hljs-string">‘root123‘</span>);</span>
<span class="hljs-operator"><span class="hljs-keyword">SET</span> PASSWORD <span class="hljs-keyword">FOR</span> <span class="hljs-string">‘root‘</span>@<span class="hljs-string">‘paas03‘</span> = PASSWORD(<span class="hljs-string">‘root123‘</span>);</span>
<span class="hljs-operator"><span class="hljs-keyword">SET</span> PASSWORD <span class="hljs-keyword">FOR</span> <span class="hljs-string">‘root‘</span>@<span class="hljs-string">‘%‘</span> = PASSWORD(<span class="hljs-string">‘root123‘</span>);</span></code>

注：亲测OK。

密码正确却登录不进

Caused by: java.sql.SQLException: Access denied for user ‘zplat_cen1’@’aifs1’ (using password: YES)

其他机器可以登录，就是安装了这个数据库的本机不能登录。然后发现不需要密码就可以登录了（去掉-p），但是只有test库。
http://www.jb51.net/article/19326.htm这里里面说的delete from user where user is NULL;是扯淡的。
真正的原因是dba没创建本主机的用户，mysql和oracle不太一样，同一个用户，需要在三台机器分别创建，包括localhost、本主机名、%（表示通配符）。应该CREATE USER ‘zplat_cen1‘@‘aifs1‘ IDENTIFIED BY ‘XXX‘;就可以了。

datetime类型有.0问题

mysql datetime类型，后面会有.0
2015-07-21 16:37:47.0
有两种解决方法：
1. 写sql时增加DATE_FORMAT(RECEIVE_TIME, ‘%Y-%m-%d %H:%i:%s‘)，这个非常麻烦，每个sql都得加
2. 修改你公司的框架代码，统一处理一下

<code class="language-java hljs "><span class="hljs-keyword">if</span> (type == Types.TIMESTAMP) { <span class="hljs-comment">// 增加对时间类型的支持,修复mysql显示.0问题 2015.7.21</span>
    Timestamp t = rs.getTimestamp(name);
    <span class="hljs-keyword">if</span> (t == <span class="hljs-keyword">null</span>)
        <span class="hljs-keyword">return</span> <span class="hljs-keyword">null</span>;
    SimpleDateFormat sDateFormat = <span class="hljs-keyword">new</span> SimpleDateFormat(<span class="hljs-string">"yyyy-MM-dd HH:mm:ss"</span>);
    String date = sDateFormat.format(t);
    <span class="hljs-keyword">return</span> date;
}</code>

参考：http://blog.csdn.net/zhanghaotian2011/article/details/7721551

同样的decimal类型也有这个问题，前台显示全部加.00

<code class="language-java hljs "><span class="hljs-keyword">if</span> (type == Types.DECIMAL) { <span class="hljs-comment">// 增加对decimal类型的支持,修复mysql显示.00问题 2015.7.22</span>
    String decimal = rs.getString(name);
    <span class="hljs-keyword">if</span> (decimal == <span class="hljs-keyword">null</span>)
        <span class="hljs-keyword">return</span> <span class="hljs-keyword">null</span>;
    <span class="hljs-keyword">if</span>(decimal.indexOf(<span class="hljs-string">"."</span>) > <span class="hljs-number">0</span>){  
        decimal = decimal.replaceAll(<span class="hljs-string">"0+?$"</span>, <span class="hljs-string">""</span>);<span class="hljs-comment">//去掉多余的0</span>
        decimal = decimal.replaceAll(<span class="hljs-string">"[.]$"</span>, <span class="hljs-string">""</span>);<span class="hljs-comment">//如最后一位是.则去掉</span>
    }
    <span class="hljs-keyword">return</span> decimal;
}</code>

PS:学好正则表达式是多么的重要！否则去0的这个代码你怎么？？

查看表占用空间

<code class="language-sql hljs "><span class="hljs-operator"><span class="hljs-keyword">SELECT</span> table_name,data_length/<span class="hljs-number">1024</span>/<span class="hljs-number">1024</span> MB <span class="hljs-keyword">FROM</span>  information_schema.tables 
<span class="hljs-keyword">WHERE</span> table_schema=<span class="hljs-string">‘zplatdb‘</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> data_length <span class="hljs-keyword">DESC</span>;</span></code>

转载请标明出处：
本文出自:【ouyida3的博客】
2015.8.3

版权声明：本文为博主原创文章，未经博主允许不得转载。

mysql的死锁等6个实战问题解决

标签：mysql