Records of problems encountered during MySQL usage and mysql usage

Records of problems encountered during MySQL usage and mysql usage

Here we will record some problems encountered during the use of MySQL and the corresponding solutions.

SQL _mode = only_full_group_by: the group by query error is reported.

Problem
When MySQL executes the group by query, the following error occurs:

#1 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by

Solution

# Set the correct SQL _mode. # log on to MySQLsudo mysql-hlocalhost-uroot-p123123SET GLOBAL SQL _mode = (select replace (@ SQL _mode, 'only _ FULL_GROUP_BY ',''));

You can also modify the MySQL configuration file and restart the MySQL service.

# Open the MySQL configuration file sudo vim/etc/mysql/conf. d/mysql. cnf # Add the following sentence SQL _mode = STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, expiration, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION at the bottom # Then restart sudo service mysql restart

Details
The default value of SQL _mode is null. In this setting, some illegal operations can be allowed, such as the insertion of some illegal data. In the production environment, this value must be set to the strict mode. Therefore, databases in the development and test environments must also be set so that problems can be found during the development and test phase. Common SQL _mode values are as follows:

ONLY_FULL_GROUP_BY: For group by aggregation operations, if the column in SELECT does not appear in group by, this SQL statement is invalid because the column is not in the GROUP BY clause.
NO_AUTO_VALUE_ON_ZERO: This value affects the insertion of auto-increment columns. By default, insert 0 or NULL to generate the next auto-growth value. If you want to insert a value of 0 and the column is auto-incrementing, this option is useful.
STRICT_TRANS_TABLES: In this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted without any restrictions on non-transaction tables.
NO_ZERO_IN_DATE: In strict mode, zero date and month are not allowed.
NO_ZERO_DATE: set this value. mysql databases do not allow zero-date insertion. An error is thrown when zero-date insertion is performed, instead of a warning.
ERROR_FOR_DIVISION_BY_ZERO: During the INSERT or UPDATE process, if the data is divided by zero, an error is generated instead of a warning. If this mode is not provided, MySQL returns NULL if the data is divided by zero.
NO_AUTO_CREATE_USER: forbid GRANT to create a user with a blank password
NO_ENGINE_SUBSTITUTION: if the required storage engine is disabled or not compiled, an error is thrown. If this value is not set, it is replaced by the default storage engine and an exception is thrown.
PIPES_AS_CONCAT: regards "|" as the concatenation operator of a string rather than the OR operator. This is the same as the Oracle database and is similar to the Concat function of the String concatenation function.
ANSI_QUOTES: After ANSI_QUOTES is enabled, the string cannot be referenced using double quotation marks because it is interpreted as an identifier.

Clear table data

Problem
Data Table clearing and data table resetting are often required for data migration or restoration.

Solution

You can use the truncate table table_name command to reset the data TABLE. The command is to delete the TABLE and then recreate the TABLE. This is not feasible for tables with constraints, the foreign key constraint must be disabled before deletion.
Another way is to delete all the data in the table, and then set the start value of the table id to 1.

# Solution 1: SELECT @ FOREIGN_KEY_CHECKS; # view the single-sign foreign key constraint command SET FOREIGN_KEY_CHECKS = 0; # first disable the foreign key constraint truncate table table_name; # Then reset the TABLE # solution 2: We recommend that you delete from table_name; # DELETE the command without the where condition DELETE all data alter table table_name AUTO_INCREMENT = 1; # reset the incremental Initial Value

MySQL memory usage is too large

When the server memory is only 1 GB or smaller, you need to change the default MySQL configuration. Otherwise, you will find that your MySQL occupies MB or even MB of memory.

By optimizing the MySQL configuration, you can reduce the memory usage. The configuration file is generally under [mysqld] in vim/etc/my. cnf. For detailed MySQL configuration items, refer to the official website: MySQL official website for detailed configuration items. The server before my order is single-core CPU and memory is 1 GB.

# The maximum number of instrumented table objects. maximum number of table instances loaded. The default value is-1 adaptive performance_schema_max_table_instances = 600 # the number of table definitions that can be stored in The definition cache, default-1 adaptive table_definition_cache = 400 # The number of open tables for all threads. maximum number of tables that can be opened by all threads. The default value is 2000 table_open_cache = 128 # innodb Engine cache size. If the startup fails, reduce innodb_buffer_pool_size = 600 M # the buffer size used by the Joint query operation. The thread exclusively occupies join_buffer_size = 8 M.

After the configuration is modified, restart the mysql service to make the configuration take effect.

You can view the current mysql configuration in the following way:

# Log on to mysqlmysql-hlocalhost-uroot-ppassword # view all global configurations show global variables; # view global configurations to show global status; # filter specific configuration items, show global variables like 'innodb % 'is the configuration item starting with innodb filtering ';

MySQL temporary table must have an alias

For temporary tables generated by queries, you must use as to define aliases, even if they are not used later. Otherwise, the following error occurs: Every derived table must have its own alias.

MySQL obtains the table column name.

Suppose the table name is table_name; you can use the following three commands to query the column names of the table.

DESC table_name;DESCRIBE table_name;SHOW columns FROM persons;