MySQL Deadlock etc 6 combat problems solved

MySQL Deadlock etc 6 combat problems solved

Directory:

• Unlocking after the lock table
• MySQL connection number is not enough
• MySQL Root password modification
• Password is correct but not logged in
• 0 issues with DateTime types
• View Table Footprint

Unlocking after the lock table

When you do a DML operation on a table, it is likely that the table is locked.
To the database host, view the process commands:
show processlist;
Locate the process ID that has the lock and kill it:
kill id;

Processlist is the whole process, a lot more, sometimes, from the processlist can see which lock, but sometimes it looks the same as other processes.
You need to look at the data table:

SELECT * FROM information_schema.INNODB_TRX;SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

Look at the trx_mysql_thread_id field, this is the deadlock process ID, and then kill it to the host.

Experience Tip: It is best not to use the client interface to modify the table structure, it may be locked. It is best to use statements. (However, in fact, I also often covet convenience, directly with SQLyog directly modify the table structure, convenient.) Occasionally locked kill can, after all, is only the development environment, production environment must not be so
Add Field: ALTER TABLE tf_b_depart ADD (PARENT_MAJOR VARCHAR(6));
To modify a field: alter table tf_f_task_target modify VALUE decimal(16,2);
Build table:CREATE TABLE td_s_salary_index (index_id VARCHAR(8));

MySQL connection number is not enough

A succession of developers in Eclipse suddenly error message:

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)

Cause: max_connections mysql default value is 100, the error will be exceeded. Restart MySQL. To prevent the back from appearing again, you need to change the max_connections to 1000 or more.

[[Email protected] ~] $more My.cnf[mysqld_multi]mysqld=/app/mysql/bin/mysqld_safemysqladmin=/app/mysql/bin/mysqladminUser= MySQLPassword= [email protected]Log=/APP/LOG/MYSQLD_MULTI.LOG[MYSQLD01]Port= 3010Socket=/tmp/mysql.sock01pid-file =/app/data_paas/db-app.pidBasedir=/app/mysqlDataDir=/app/data_paasUser= Mysqlsymbolic-links=0character-set-server=utf8Lower_case_table_names=1innodb_log_file_size=128minnodb_log_buffer_size=4minnodb_buffer_pool_size=1gEvent_scheduler=1explicit_defaults_for_timestampmax_connections=1500join_buffer_size= 128Msort_buffer_size= 10Mread_rnd_buffer_size= 2M

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

MySQL Root password modification

 SELECT *  from MySQL. User WHERE USER=' root '; SET PASSWORD  for ' root '@' localhost ' = PASSWORD (' root123 '); SET PASSWORD  for ' root '@' paas03 ' = PASSWORD (' root123 '); SET PASSWORD  for ' root '@'% ' = PASSWORD (' root123 ');

Note: Pro-Test OK.

Password is correct but not logged in

caused by:java.sql.SQLException:Access denied for user ' zplat_cen1 ' @ ' AIFS1 ' (using Password:yes)

Other machines can log on, that is, the local computer that installed the database cannot log on. Then you can log in without a password (minus-p), but only the test library.
Http://www.jb51.net/article/19326.htm here delete from user where user is NULL; is talking nonsense.
The real reason is that DBAs do not have the user to create this host, MySQL and Oracle are not the same, the same user needs to be created on three machines, including localhost, this hostname, and% (for wildcard characters). It should be CREATE USER ‘zplat_cen1‘@‘aifs1‘ IDENTIFIED BY ‘XXX‘; all right.

The datetime type has a. 0 problem

MySQL datetime type, followed by a. 0
2015-07-21 16:37:47.0
There are two ways to resolve this:
1. When writing SQL, DATE_FORMAT(RECEIVE_TIME, ‘%Y-%m-%d %H:%i:%s‘) This is very troublesome, each SQL has to be added
2. Revise your company's framework code and handle it uniformly

if// 增加对时间类型的支持,修复mysql显示.0问题 2015.7.21    Timestamp t = rs.getTimestamp(name);    ifnull)        returnnull;    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");    String date = sDateFormat.format(t);    return date;}

Reference: http://blog.csdn.net/zhanghaotian2011/article/details/7721551

The same decimal type also has this problem, the front desk shows all plus. 00

if// 增加对decimal类型的支持,修复mysql显示.00问题 2015.7.22    String decimal = rs.getString(name);    ifnull)        returnnull;    if(decimal.indexOf("."0){          decimal = decimal.replaceAll("0+?$""");//去掉多余的0        decimal = decimal.replaceAll("[.]$""");//如最后一位是.则去掉    }    return decimal;}

PS: How important it is to learn regular expressions! Otherwise go to 0 of this code you how??

View Table Footprint

SELECT table_name,data_length/1024/1024 MB FROM  information_schema.tables WHERE table_schema=‘zplatdb‘ ORDER BY data_length DESC;

Reprint please indicate the source:
This article is from: "Ouyida3 's Blog"
2015.8.3

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL Deadlock etc 6 combat problems solved