Tag Archives: mysql

Treatment of MySQL database keyword as column name by mybatisplus — sqlsyntax errorexception: you have an error in your SQL syntax;

explain:

When designing the database, if you use the MySQL keyword as the column name (for example, order is used for sorting), an error will be reported: java.sql.sqlsyntax errorexception: you have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ORDER,CREATE_ TIME,MODIFY_ TIME FROM xxx’ at line 1

terms of settlement:

Modify the column name, do not use the keyword as the column name; (recommended)

When querying, use quotation marks (single quotation marks, double quotation marks and back quotation marks) to enclose the column name of its keyword,

If you use mybatis to write SQL, there is no problem. You can use quotation marks to solve it;

If you use mybatis plus, when querying all columns, you need to list all columns, because the corresponding column names containing keywords need to be processed.

How to Solve Navicat Remote Connection to MySQL is Stuck

Developing an application system to connect to the MySQL database of the company’s test server is very slow, but connecting to the local MySQL database is very fast. At first, it was thought that it may be caused by network connection problems. After Ping and routing, it was found that the network communication is normal, and the local connection on the MySQL machine is very fast, Therefore, the network problem has been basically eliminated, so I want to see if it is a MySQL configuration problem. After querying MySQL related documents and network search, it seems that a configuration can solve such a problem, that is, add the following configuration parameters to the MySQL configuration file:

[mysqld]
skip-name-resolve

Under Linux, the configuration file is/etc/my.cnf. Under windows, the configuration file is my.ini file under MySQL installation directory. Note that the configuration is added under [mysqld]. After changing the configuration and saving it, restart MySQL and conduct remote connection test. Everything is restored as before.

MySQL service failed to read description, error code: 2

Scenario: when 360 is used to remove garbage, a MySQL configuration file is deleted (PS: it should be the registry), resulting in a prompt: “MySQL service failed to read the description, error code: 2”

terms of settlement:

Detailed steps:

CMD administrator starts
switch to the database bin directory
enter the command SC delete MySQL
restart the computer
repeat actions 1 and 2
enter mysqld.exe – install
net start MySQL
precautions:

Restart the computer after the third step
in the sixth step, enter mysqld.exe – install instead of mysql.exe – install

 

Original link: https://blog.csdn.net/weixin_ 42437102/article/details/106380319

 

[Solved] MYSQL Error: Cannot delete or update a parent row: a foreign key constraint fails

1 problem
when deleting data or tables associated with foreign keys, MySQL reports an error:

Cannot delete or update a parent row: a foreign key constraint fails

2 solution

SET foreign_key_checks = 0; // First set the foreign key constraint check off

drop table mytable; // Delete the data, table or view

SET foreign_key_checks = 1; // Turn on foreign key constraint checking to maintain the structural integrity of the table
First turn off foreign key constraints, perform the delete operation, and then turn on foreign key constraints

MYSQL Error: Out of sort memory, consider increasing server sort buffer size; nested exception is java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size

MYSQL Error: Out of sort memory, consider increasing server sort buffer size; nested exception is java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size
The Java interface suddenly reports an error with the error message Out of sort memory, consider increasing server sort buffer size.
The literal meaning is Out of sort memory, consider increasing server sort buffer_size.
mysql> show variables like ‘%sort_buffer_size%’;
+————————-+———+
| Variable_name | Value |
+————————-+———+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size | 262144 |
+————————-+———+
3 rows in set (0.01 sec)
You can see the sort_buffer_size, at this time you need to adjust the corresponding data larger
mysql> SET GLOBAL sort_buffer_size = 1024*1024;
Query OK, 0 rows affected (0.00 sec)

Retest

 

Error connecting mysql: server returns invalid timezone. Go to ‘Advanced ‘tab and set ‘server Timezon ‘

https://blog.csdn.net/x947096828/article/details/104665383

 

 

Method 1:

Directly in the following figure,?After that, add servertimezon = UTC, click apply, and then try test connection again to connect successfully

 

 

Method 2:

Go to the “advanced” tab, manually set the “servertimezon” attribute value to UTC, then click apply and try test connection again to successfully connect

[Solved] MySQL Error: ERROR 1615 (HY000): Prepared statement needs to be re-prepared

Today’s company’s project view query reported an error. Find the reason for the code and view, and find that there is no problem, and the view will make an error. Errors are reported as follows:

ERROR 1615 (HY000): Prepared statement needs to be re-prepared

Many cases are caused by unreasonable setting of MySQL variable values. Adjust the following values:

table_ open_ cache 64=16384
table_ definition_ cache 256=16384
mysql set global table_ open_ cache=16384;

mysql set global table_ definition_ cache=16384;

Error 1615 (HY000): prepared statement needs to be re prepared occurs suddenly in a stored procedure that can normally run

It may be caused by unreasonable MySQL configuration parameters. The solutions are as follows

SHOW VARIABLES LIKE ‘%table_ open_ cache%’;
Variable_ name                           Value
table_ open_ cache                     two thousand
table_ open_ cache_ instances     one

SHOW VARIABLES LIKE ‘%table_ definition_ cache%’;
Variable_ name               Value
table_ definition_ cache   four thousand

adjust variable value
— table_ open_ cache
SET GLOBAL table_ open_ cache=16384;
SET GLOBAL table_ definition_ cache=16384;