current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help

MySQL


Error codes All Versions

edit move examples delete
1.0
3.19
3.20
3.21
3.22
3.23
4.0
4.1
5.0
5.1
5.5
5.6
5.7

This draft deletes the entire topic.

Introduction

Introduction

edit
expand all collapse all

Examples

  • 6
    Error code 1064: Syntax error
    edit move example delete
    6 
    select LastName, FirstName,
from Person

    Returns message:

    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.

    Getting a "1064 error" message from MySQL means the query cannot be parsed without syntax errors. In other words it can't make sense of the query.

    The quotation in the error message begins with the first character of the query that MySQL can't figure out how to parse. In this example MySQL can't make sense, in context, of from Person. In this case, there's an extra comma immediately before from Person. The comma tells MySQL to expect another column description in the SELECT clause

    A syntax error always says ... near '...'. The thing at the beginning of the quotes is very near where the error is. To locate an error, look at the first token in the quotes and at the last token before the quotes.

    Sometimes you will get ... near ''; that is, nothing in the quotes. That means the first character MySQL can't figure out is right at the end or the beginning of the statement. This suggests the query contains unbalanced quotes (' or ") or unbalanced parentheses or that you did not terminate the statement before correctly.

    In the case of a Stored Routine, you may have forgotten to properly use DELIMITER.

    So, when you get Error 1064, look at the text of the query, and find the point mentioned in the error message. Visually inspect the text of the query right around that point.

    If you ask somebody to help you troubleshoot Error 1064, it's best to provide both the text of the whole query and the text of the error message.

    edited Jan 3 at 10:22
  • 2
    Error code 1175: Safe Update
    edit move example delete
    2

    This error appears while trying to update or delete records without including the WHERE clause that uses the KEY column.

    To execute the delete or update anyway - type:

    SET SQL_SAFE_UPDATES = 0;

    To enable the safe mode again - type:

    SET SQL_SAFE_UPDATES = 1;
    edited Sep 29 '16 at 21:44
  • 1
    Error code 1215: Cannot add foreign key constraint
    edit move example delete
    1

    This error occurs when tables are not adequately structured to handle the speedy lookup verification of Foreign Key (FK) requirements that the developer is mandating.

    CREATE TABLE `gtType` (
  `type` char(2) NOT NULL,
  `description` varchar(1000) NOT NULL,
  PRIMARY KEY (`type`)
) ENGINE=InnoDB;

CREATE TABLE `getTogethers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` char(2) NOT NULL,
  `eventDT` datetime NOT NULL,
  `location` varchar(1000) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gt2type` (`type`), -- see Note1 below 
  CONSTRAINT `gettogethers_ibfk_1` FOREIGN KEY (`type`) REFERENCES `gtType` (`type`)
) ENGINE=InnoDB;

    Note1: a KEY like this will be created automatically if needed due to the FK definition in the line that follows it. The developer can skip it, and the KEY (a.k.a. index) will be added if necessary. An example of it being skipped by the developer is shown below in someOther.

    So far so good, until the below call.

    CREATE TABLE `someOther` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `someDT` datetime NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `someOther_dt` FOREIGN KEY (`someDT`) REFERENCES `getTogethers` (`eventDT`)
) ENGINE=InnoDB;

    Error Code: 1215. Cannot add foreign key constraint

    In this case it fails due to the lack of an index in the referenced table getTogethers to handle the speedy lookup of an eventDT. To be solved in next statement.

    CREATE INDEX `gt_eventdt` ON getTogethers (`eventDT`);

    Table getTogethers has been modified, and now the creation of someOther will succeed.

    From the MySQL Manual Page Using FOREIGN KEY Constraints:

    MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

    Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

    InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

    Note that last point above about first (left-most) columns and the lack of a Primary Key requirement (though highly advised).

    Upon successful creation of a referencing (child) table, any keys that were automatically created for you are visible with a command such as the following:

    SHOW CREATE TABLE someOther;

    Other common cases of experiencing this error include, as mentioned above from the docs, but should be highlighted:

    • Seemingly trivial differences in INT which is signed, pointing toward INT UNSIGNED.

    • Developers having trouble understanding multi-column (composite) KEYS and first (left-most) ordering requirements.

    created Jul 26 '16 at 18:25
+ Add Example
Please consider making a request to improve this example.

Syntax

Syntax

edit

Parameters

Parameters

edit

Remarks

Remarks

edit
edited Jan 6 at 18:28
+4
Still have a question about Error codes? Ask Question
Blog
Say Farewell to Winter Bash 2016!

Topic Outline


    Save Draft Discard