MySQL Error codes 126, 1054, 1146, 1062, 24
Example
(taking a break) With the inclusion of those 4 error numbers, I think this page will have covered about 50% of the typical errors users get.
(Yes, this 'Example' needs revision.)
24 Can't open file (Too many open files)
open_files_limit comes from an OS setting. table_open_cache needs to be less than that.
These can cause that error:
-
Failure to
DEALLOCATE PREPAREin a stored procedure. -
PARTITIONed table(s) with a large number of partitions and innodb_file_per_table = ON. Recommend not having more than 50 partitions in a given table (for various reasons). (When "Native Partitions" become available, this advice may change.)
The obvious workaround is to set increase the OS limit:
To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent).
Then increase open_files_limit and table_open_cache.
As of 5.6.8, open_files_limit is auto-sized based on max_connections, but it is OK to change it from the default.
1062 - Duplicate Entry
This error occur mainly because of the following two reasons
-
Duplicate Value -
Error Code: 1062. Duplicate entry ‘12’ for key ‘PRIMARY’The primary key column is unique and it will not accept the duplicate entry. So when you are trying to insert a new row which is already present in you table will produce this error.
To solve this, Set the primary key column as
AUTO_INCREMENT. And when you are trying to insert a new row, ignore the primary key column or insertNULLvalue to primary key.
CREATE TABLE userDetails(
userId INT(10) NOT NULL AUTO_INCREMENT,
firstName VARCHAR(50),
lastName VARCHAR(50),
isActive INT(1) DEFAULT 0,
PRIMARY KEY (userId) );
--->and now while inserting
INSERT INTO userDetails VALUES (NULL ,'John', 'Doe', 1);
-
Unique data field -
Error Code: 1062. Duplicate entry ‘A’ for key ‘code’You may assigned a column as unique and trying to insert a new row with already existing value for that column will produce this error.
To overcome this error, use
INSERT IGNOREinstead of normalINSERT. If the new row which you are trying to insert doesn't duplicate an existing record, MySQL inserts it as usual. If the record is a duplicate, theIGNOREkeyword discard it without generating any error.
INSERT IGNORE INTO userDetails VALUES (NULL ,'John', 'Doe', 1);
