Summary of various issues in MySQL usage

Now there are more and more mysql database users and more problems. Next I will summarize some common solutions to the problems encountered during mysql usage.

MySQL Workbench unavailable

I used the XAMPP MySQL component before. It was relatively simple to use in the past. I just imported the database and used it directly on the Web. I didn't find any problems with this component, I accessed this database using MySQL Workbench today and found various errors. It mainly means that a strange directory under/var/folders/has no read and write permissions, after the directory read/write permission is changed, the error persists. I guess it was my own reason for Workbench, and the new version that was downloaded later cannot be used. Check the err file in the MySQL DATA folder and find that the MySQL service component has reported the n-many error. It seems that this server is no longer ready. Re-download an official MySQL for Mac version, mysql-5.5.17-osx10.6-x86.dmg, installed to the Library also mounted up, Workbench actually good.

From this point of view, when there is a problem with the Workbench, do not worry about the Workbench first, to see if your library is already in danger.

MySQL access permission

MySQL is an idiot. 127.0.0.1 and localhost are isolated from each other in the permission table. When I changed the password, I changed only one line at the beginning and changed localhost to %. It was good to log in, but I couldn't see the database, which was a crash. After that, it was normal to reset both lines. In addition, if you add an external access %, it is best to add a single line, do not delete the original, otherwise it will be easy to confuse.

If you create a new user and activate the access permission, write localhost and %.

Grant all privileges on *. * TO 'user' @ 'localhost' identified by 'Password' with grant option;

Grant all privileges on *. * TO 'user' @ '%' identified by 'Password' with grant option;

Flush privileges;

MySQL JDBC connection

The connection method for JDBC access to MySQL is a bit strange. It can be accessed normally with 127.0.0.1, but localhost will be rejected. After a rough look at the reason, if it is localhost, the local IP address will be used for direct access during connection. If the IP address is not in the MySQL access permission list, it will be rejected. This problem does not occur below PHP, so we should analyze the underlying causes when we are free.

MySQL Encoding

MySQL encoding is always very disgusting. Start with the default configuration and insert Chinese characters into the program. The prompt is error 1336 (HYOOO): Incorrect string values :...

First, consider the server encoding problem. In my. cnf's [mysqld], add:

Character_set_server = utf8

In MySQL, verify that the character set has been changed to utf8:

Mysql> show variables like 'character % ';

+ -------------------------- + ----------------------------------------------------- +

| Variable_name | Value |

+ -------------------------- + ----------------------------------------------------- +

| Character_set_client | utf8 |

| Character_set_connection | utf8 |

| Character_set_database | utf8 |

| Character_set_filesystem | binary |

| Character_set_results | utf8 |

| Character_set_server | utf8 |

| Character_set_system | utf8 |

| Character_sets_dir |/usr/local/mysql-5.5.17-osx10.6-x86/share/charsets/|

+ -------------------------- + ----------------------------------------------------- +

However, Chinese data still cannot be inserted, and errors remain. To solve the Character Set Problem of the database and table, change the character set of the database:

Alter database db_name default character set utf8;

Still not working. Change the character set of the original table:

Alter table temp_table default character set utf8;

Continue to change the default character set and all character columns (CHAR, VARCHAR, TEXT) of the table to the new character set:

Alter table temp_table convert to character set utf8;

Check the character set of each column:

Mysql> show full columns from temp_table;

+ -------- + -------------- + ----------------- + ------ + ----- + --------- + ------- + ------------------------------- + ----------- +

| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |

+ -------- + -------------- + ----------------- + ------ + ----- + --------- + ------- + ------------------------------- + ----------- +

| No | varchar (100) | utf8_general_ci | YES | NULL | select, insert, update, references |

| Name | varchar (100) | utf8_general_ci | YES | NULL | select, insert, update, references |

| Price | varchar (100) | utf8_general_ci | YES | NULL | select, insert, update, references |

| Count | varchar (100) | utf8_general_ci | YES | NULL | select, insert, update, references |

| Date | varchar (100) | utf8_general_ci | YES | NULL | select, insert, update, references |

| Source | varchar (100) | utf8_general_ci | YES | NULL | select, insert, update, references |

| Export | varchar (100) | utf8_general_ci | YES | NULL | select, insert, update, references |

+ -------- + -------------- + ----------------- + ------ + ----- + --------- + ------- + ------------------------------- + ----------- +

7 rows in set (0.01 sec)

Finally normal!

In addition, if there is still a problem connecting JDBC or PHP to MySQL Chinese after the MySQL server is set up, it is mostly a Conn Character Set problem. It is best to add one sentence before the query starts: set names 'utf8'; this sentence is equivalent:

SET character_set_client = utf8;

SET character_set_results = utf8;

SET character_set_connection = utf8;