Database problems encountered today __ database

a good run before the program, today suddenly can not run, background error: illegal mix of collations (utf8_unicode_ci,implicit) and (utf8_general_ci,implicit) for Operation ' = '

After the check is the back of the colleagues backstage last night data, the results of the problem, the table's character set collation code format different

Solution:

With the database Tools, the table and the table in each field of the character set of the collation are modified to the same, generally by default are UTF8_GENERAL_CI

problem two: MySQL query results are not case-sensitive: page situation

WORKAROUND: Modify the SQL statement:

Modified results:

Third, the command line to connect to the remote database

====================== suddenly had this problem today ==================================================================

The database is not connected immediately, so hurry up.

1. See if the disk is full

command: Df-h

2. Restart MySQL database, free space

finally summed up the problem, the database has been running for two years, also did not move, should be the log file is full, it may be that I executed an error script to cause

The

received an alert from MySQL error log monitoring at noon today, with the error log:

101209 13:13:32 [ERROR]/usr/local/mysql/bin/mysqld:the table ' test_1291870945841162 ' is full
101209 13:13:32 [ERROR]/usr/local/mysql/bin/mysqld:the table ' Test_ 1291870945841162 ' is full
101209 13:13:32 [ERROR]/usr/local/mysql/bin/mysqld:the table ' test_1291870945841162 ' is Full
101209 13:13:32 [ERROR]/usr/local/mysql/bin/mysqld:the table ' test_1291870945841162 ' are full

Through the query MySQL official site:
http://dev.mysql.com/doc/refman/5.0/en/full-table.html:
Because the system is Linux, there is no restrictions on the operating system and file formats, You can tell by the name of the table that the table should be a temporary table, and that the table cannot be found in the database.

It is important to have this sentence:
You are using the MEMORY (HEAP) storage engine, in the case you are need to increase the value of the MA X_heap_table_size system variable. 5.1.3, "Server System Variables".


Then modify the MySQL configuration file/etc/my.cnf to add/modify two lines under [mysqld]:
tmp_table_size = 256M
Max_heap_table_size = 256M

System defaults to 16M, don't forget to restart MySQL, you can dynamically modify this parameter online, after these days of observation, the world is quiet a lot.

-------------------------another article-------------------------------

When we want to write new data and the "The table is full" alarm error, do not worry, follow the following ideas to step through the analysis can be:

1, view the operating system and MySQL error log files

Verify that the operating system's file system does not have an error, and that there are some of the most intuitive, visible errors in the MySQL error log file.

It is possible that the database file exceeds the file size limit of the operating system layer, such as Fat/fat32 and the lower version of Linux, the file maximum can not be greater than 2G (maximum expansion to 4G), which requires converting FAT32 to NTFS, or upgrade the Linux version.

2, verify that the disk space is not full

Perform df-h to view the remaining disk space, and remove unwanted files as soon as you find that the disk space is indeed exhausted.

If you calculate the sum of each directory by Du and find that you do not run out of disk space at all, you need to be aware that a deleted file may not be fully released, causing DF to appear to have run out, but du is not statistically.

This can be done lsof | grep-i deleted find the deleted large file, kill its corresponding process, release the file descriptor.

If the process cannot be killed, for example, if the mysqld process is occupied, you can find in MySQL which internal thread is in use, stop the thread.

There has been an example of this:

With vim to open the MySQL slow query log, the exit when selected "Wq" instructions, that is, save exit, the result of tragedy occurred.

Because during the period of its opening, slow query log has a new log generated, will continue to write, but he quit with the way to save the exit, into a "new" file (or the new file handle file handler), the "new" file can not be recognized by the MYSQLD process,

The mysqld process still writes slow query log to the file (or file handle) that it opened, and the log file continues to grow, but the manually saved exit file is no longer growing, and viewing the file directly does not see any exceptions.

At this time can only use lsof-p ' pidof mysqld ' to see the file.

The solution is simply to back up the original file and execute the following instructions:

FLUSH Slow LOGS;

Note: MySQL 5.5 to support the beginning of binary/engine/error/general/relay/slow and other keywords, the previous version can only refresh all the log.

3. Confirm Data Table Status if it's a MyISAM engine,

By default, the MyISAM engine can support up to 256TB ( myisam_data_pointer_size = 6,256^6 = 256TB), unless there is a limit to the operating system layer.

In MySQL5.0, MyISAM engine Row Records default to dynamic lengths, with a single table up to a maximum of 256tb,myisam row pointers (myisam_data_pointer_size) length of 6 bytes.

Before this, the MyISAM row pointer defaults to 4 bytes and supports only 4GB of data. The maximum size of the row pointer can be set to 8 bytes.

To increase the maximum capacity of the MyISAM table, you can modify the values of the table definition settings max_rows when the row pointer setting is less than enough.

ALTER TABLE ' xx ' Engine=myisam max_rows=nn

Note: in the table definition, the avg_row_length property defines the maximum length of the Blob/text field type. if it's a InnoDB engine,

ibdata* shared table Space the last file was not set to grow, or it exceeded the single file size limit for the 32-bit system.

Workaround:

1, ibdata* the last file (not the last file can be set to automatic growth ) set to automatic growth;

2, check the operating system, migrate to 64-bit operating system;

3, turn into independent table space;

4, delete historical data, restructure the table space; if it's a memory engine ,

1, appropriate to improve the max_heap_table_size settings (note that the value is session level, do not set too large, such as 1GB, generally do not recommend more than 256MB);

2, execute Alter TABLE T_MEM engine=memory; Restructure the table space, otherwise unable to write new data ;

3, delete part of historical data or directly empty, to restructure the table space;

4, set Big_tables = 1 , all temporary tables stored in the disk, rather than in memory, the disadvantage is that if a SQL execution needs to use a temporary table, the performance will be much worse;

By the way, if the datasheet has a list of self-increasing int, but the ID value reaches the maximum of int, the alarm information for the MyISAM, MEMORY, InnoDB three engines is different.

The alarm information for the InnoDB engine is similar to this:

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

The MyISAM and memory engines are like this: ERROR 1062 (23000): Duplicate entry ' 4294967295′for key ' PRIMARY '