Error code 1064: Syntax error

Suppose you have an SQL statement

select LastName, FirstName,from Person
Copy the code

Package errors occur during execution

Error Code: 1064. 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 'from Person' at line 2.
Copy the code
  • Error 1064 indicates that there is a syntax error in your SQL statement.
  • At the end of the error message, there is a single quoted reference to the source SQL statement ‘from Person’, which means that the SQL statement cannot be parsed, but for our example, the error reference is useless. Note that the information in this reference is preceded by a comma, which should be followed by the column name in the table, not the from keyword.
  • 1064 error messages usually end with… near ‘… The reference after NEAR is where the SQL statement cannot be parsed at the beginning. When this error occurs, observe the characters before and after the SQL statement that cannot be parsed.
  • Sometimes, the error message you get is… Near “, the reference after near is empty, which means that the error is at the beginning or the first character of the SQL statement. Usually, the single quotation mark, quotation mark, parenthesis are not paired, or the correct character is not at the end of the SQL statement, such as Chinese semicolon.
  • If error 1064 is found, check the SQL statement referenced in the error message, and check the parts before and after the SQL statement.
  • If someone asks you for a 1064 SQL error, you’d better ask them to provide you with the full SQL statement and error message.

Error code 1175: Safe Update

This error is caused when you execute an UPDATE or delete statement without specifying a WHERE condition. If you want to ignore this error, modify the configuration

SET SQL_SAFE_UPDATES = 0;
Copy the code

Re-open the error alert

SET SQL_SAFE_UPDATES = 1;
Copy the code

1067, 1292, 1366, 1411 – Bad Value for number, date, default, etc.

  • 1067 This error is related to the TINESTAMP default value, please check the official documentation
  • 1292/1366 Double and INTEGER type error, check syntax and numeric type
  • 1292 detatime error, check whether the time data format inserted is out of range, time string format with time zone format is not correct
  • 1292 VARIABLE Check the VARIABLE property you set
  • 1292 LOAD DATA Check the escape character, check the DATA type
  • 1411 STR_TO_DATE Check time String format

1045 Access denied

If the permission is incorrect, check whether the user name and password are correct and whether the current user has the permission to access data.

1236 “impossible position” in Replication

  • In most cases, this is because the primary mysql node is down and sync_binlog=OFF. The solution is to set POS=0 on the secondary node.
  • When sync_binlog=OFF, the primary node sends data to the secondary node before writing to the binlog. When the master node fails to write the binlog, the data has been sent to the slave node, so the slave node updates the binlog after writing the data, causing the position of the binlog Pointers on the master node and the slave node to be inconsistent. Therefore, when the master node is restarted, a new binlog will be started, so set the binlog position of the slave node to 0 and start from scratch.
  • The best solution is to set sync_binlog=ON, which is binlog-based synchronization but incurs a lot of I/O overhead.

24 Can’t open file (Too many open files)

Open_files_limit is a system setting, and table_open_cache must be smaller than this system setting

1062 – Duplicate Entry

1. Primary key constraint Error Code: 1062. Duplicate entry ’12’ for key ‘PRIMARY’, Duplicate entry ’12’ for key ‘PRIMARY’, Duplicate entry ’12’ for key ‘PRIMARY’ must be unique. 2. Unique attribute constraint, Error Code: 1062. Duplicate entry ‘A’ for key ‘code’, Duplicate entry ‘A’ for key ‘code’, Duplicate entry ‘A’ for key ‘code’ If it does, no operation is performed and no error is reported. If it does not repeat, it is the same as the INSERT behavior.

126, 127, 134, 144, 145

You may encounter these errors when accessing data. This error is caused by an internal error in the mysql database. Such as:

MySQL error code 126 = Index file is crashed MySQL error code 127 = Record-file is crashed MySQL error code 134 = Record  was already deleted (or record file crashed) MySQL error code 144 = Table is crashed and last repair failed MySQL error  code 145 = Table was marked as crashed and should be repairedCopy the code

Mysql bugs, attacks, service hangs, incorrect shutdown of mysql, corrupted data can cause these problems. When these errors occur, the data becomes inaccessible and remains inaccessible permanently. So, it’s best to make a backup of your data. If you don’t have a backup, try to fix mysql. If the storage engine is MyISAM, run the CHECK TABLE and REPAIR TABLE commands (mysql>=5.7).

CHECK TABLE <table name> ////To check the extent of database corruption
REPAIR TABLE <table name> ////To repair table
Copy the code

1366

This usually means inconsistent character set processing between clients and servers.

139

Error 139 May indicate that the number and size of fields in the table definition exceeded some limits. Check for unusually long strings, unusually large integers, and so on in SQL statements

2002, 2003 Cannot connect

Check whether the firewall is faulty. Disable the firewall. Check whether the MYSQL server is listening to the IP address

2014 Commands out of sync; you can’t run this command now

This was caused by an incorrect sequence of SQL queries

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called     mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.
Copy the code

In summary, this means that you query the results, but you don’t get the results down. Mysql Server has been waiting for you to retrieve the result.

1215: Cannot add foreign key constraint

Error Adding a foreign key. Check whether the data types of the two fields associated with the foreign key are the same.