Mysql deadlock and other six practical problems solved _ MySQL

Mysql deadlock and other six practical issues to solve the directory: the number of mysql connections to be unlocked after the lock table is insufficient. the mysql root password is correct, but the password is correct, but the logon fails to enter the datetime type. if there is no problem, check the unlocked table after the Space lock table is occupied. when dml operations are performed on the table, it is likely that the table is deadlocked by mysql and six other practical problems are solved.

Directory:

The number of mysql connections unlocked after the lock table is insufficient. the root password of mysql is correct, but the password cannot be entered in the datetime type. there is no problem. check the occupied space of the table.

Unlock after lock table

When you perform dml operations on a table, it is likely that the table is locked.
Go to the database host and view the process command:
Show processlist;
Find the lock process id and kill it:
Kill id;

Processlist is a large number of processes. sometimes, you can see which lock is available in processlist, but sometimes it looks the same as other processes.
You need to view the data table:

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

Check the trx_mysql_thread_id field. this is the id of the deadlock process and kill it on the host.

Tip: It is best not to use the client interface to modify the table structure, which may be locked. It is best to use statements. (However, in fact, I often look forward to the convenience of using SQLyog to directly modify the table structure. Occasionally, kill is locked. after all, it is only the development environment, and the production environment must not be like this)
ADD Field: alter table tf_ B _depart ADD (PARENT_MAJOR VARCHAR (6 ));
Modify field: alter table tf_f_task_target modify VALUE decimal );
Create table: create table td_s_salary_index (index_id VARCHAR (8 ));

Insufficient mysql connections

One after another, developers suddenly report errors in 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)

Cause: The default value of max_connections mysql is 100. if it is exceeded, an error is returned. Restart mysql. To prevent subsequent occurrences, you need to change max_connections to 1000 or more.

[mysql@paas03 ~]$more my.cnf[mysqld_multi]mysqld = /app/mysql/bin/mysqld_safemysqladmin = /app/mysql/bin/mysqladminuser = mysqlpassword = a@Aug22log=/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

Modify the root password of mysql

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: The test is OK.

The password is correct but the logon fails.

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

Other machines can log on, that is, the local machine installed with this database cannot log on. You can log on without a password (remove-p), but only the test database is available.
Http://www.bitscn.com/article/19326.htmthe delete from user where user is NULL here is a nonsense.
The real reason is that the dba did not create the user of the host, mysql and oracle are not the same, the same user needs to be created on three machines respectively, including localhost, local host name, and % (wildcard ). Create user 'zplat _ cen1' @ 'afs1' identified by 'XXX.

The datetime type has a. 0 problem.

Mysql datetime type, followed by. 0
2015-07-21 16:37:47. 0
There are two solutions:
1. add DATE_FORMAT (RECEIVE_TIME, '% Y-% m-% d % H: % I: % S') when writing SQL statements. this is very troublesome and every SQL statement has to be added
2. modify the framework code of your company and handle it in a unified manner.

If (type = Types. TIMESTAMP) {// added support for the time type to fix mysql display. 0 problem 2015.7.21 Timestamp t = rs. getTimestamp (name); if (t = null) return null; SimpleDateFormat sDateFormat = new SimpleDateFormat ("yyyy-MM-dd HH: mm: ss"); String date = sDateFormat. format (t); return date ;}

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

This problem also occurs for the same decimal type. the front-end displays that all values are added with. 00.

If (type = Types. DECIMAL) {// added support for the decimal type to fix mysql display. 00 problem 2015.7.22 String decimal = rs. getString (name); if (decimal = null) return null; if (decimal. indexOf (". ")> 0) {decimal = decimal. replaceAll ("0 +? $ "," "); // Remove unnecessary 0 decimal = decimal. replaceAll ("[.] $ "," "); // if the last digit is. remove} return decimal ;}

PS: how important it is to learn regular expressions! Otherwise, what do you do with the code 0 ??

View the space occupied by a table

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