(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:
DEALLOCATE PREPARE in 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
/etc/security/limits.conf or in
sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent).
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 insert
NULLvalue 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 normal
INSERT. 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, the
IGNOREkeyword discard it without generating any error.
INSERT IGNORE INTO userDetails VALUES (NULL ,'John', 'Doe', 1);