'MariaDB' 카테고리의 글 목록

[MySQL || MariaDB] System,SQL ERROR 모음 원인 및 해결 총 정리

2020.04.24
[MySQL || MariaDB] TIMESTAMP와 DATETIME 차이점 총 정리

2020.04.19
[MySQL || MariaDB] ALTER TABLE 문법 총 정리

2020.03.22
[MySQL || MariaDB] 숫자형 문자 정렬 문제 및 해결(Feat.varchar)

2020.03.15
[MySQL || MariaDB] 여러 테이블 한번에 카운터 하기

2020.03.15
[MySQL || MariaDB] ORDER BY 특정 값 우선 정렬(feat.FIELD)
2

2020.02.29
[MySQL || MariaDB] InnoDB 총 정리
1

2019.12.28
[MySQL || MariaDB] 데이터베이스 접근 권한 거부 Host 'IP' is not allowed to connect to this MySQL server
1

2019.11.19

System 오류

1. ERROR 2002 : can't connect to local mysql server through socket '/var/lib/mysql/mysql.sock' (2) (13) (111)

이 문제는 메시지 내용은 같으나, 뒤에 숫자에 따라 해결법이 다릅니다.
오류 메시지를 정확히 보셔야 합니다. (2) (13) (111)

※ 주의 : mysql.sock 관련 에러가 뜨는 원인은 굉~장히 다양하므로 명확한 해결책을 제시하기 어렵습니다.

최선책을 올립니다.

ERROR 2002 : can't connect to local mysql server through socket '/var/lib/mysql/mysql.sock' (2)

원인 :
1. MySQL 데몬이 기동이 되어 있지 않은 경우 (MySQL 서버가 실행되지 않은 것)
2. my.cnf 파일 자체가 깨졌을 경우 해당 메시지를 만난다.
3. mysql.sock를 시스템이 못 찾거나 경로가 정확하지 않아서 발생하는 오류

해결 :

mysql.sock 위치 찾기
find / -name mysql.sock

/usr/local/mysql/bin/mysql -u root -p mysql -S /var/lib/mysql/mysql.sock
or 둘 중 택 1
cd /usr/local/mysql 
./bin/mysql -u root -p mysql -S /var/lib/mysql/mysql.sock

원인02 :

MySQL 유저로 접근했을 경우 mysql.sock 파일이 있는 디렉토리에 접근을 할 수 없어서 나오는 오류

해결 02 :

service mysqld stop
chmod 755 -R /var/lib/mysql/
chown mysql:mysql -R /var/lib/mysql/
service mysqld start

ERROR 2002 : can't connect to local mysql server through socket '/var/lib/mysql/mysql.sock' (13)

원인 : /usr/local/mysql 디렉터리 권한 문제

해결 :

chmod 777 /usr/local/mysql

ERROR 2002 : can't connect to local mysql server through socket '/var/lib/mysql/mysql.sock' (111)

원인 : 심볼릭 링크가 잘못 됐을 경우

해결 :

ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

그 외 mysql.sock 문제 관련 해결책 : my.cnf 파일에 mysql.sock 경로 지정하기

vi /etc/my.cnf
## /etc/my.cnf
[client]
socket = /var/lib/mysql/mysql.sock
[mysqld]
socket = /var/lib/mysql/mysql.sock

ERROR access to database denied (using password: YES)

원인 : 데이터베이스에 접근할 수 없는 권한을 가진 user 가 접속한 경우.

해결 :

GRANT ALL PRIVILEGES ON *.*TO '권한 허용할 유저 아이디'@'%' IDENTIFIED BY 'password' with GRANT OPTION;
FLUSH PRIVILEGES;

원인02 : 데이터베이스 로그인 시도에 접속 비밀번호가 틀린 경우

해결 02 : 비밀번호를 올바르게 입력한다.

ERROR 1044 : access denied for user : 'abc@ localhost' to database 'abcdb'

원인 :
'abc' 유저에 대해서 'abcdb'라는 이름의 데이터베이스에 접근할 수 없기 때문에 발생하는 에러.

원인 발생 예시 : mysql> use abcdb

해결 : 'abc'라는 유저가 'abcdb'에 접근할 수 있도록 접근 허용 SQL 문법을 실행한다.

grant all privileges on userdb.* to 'abc'@'localhost' identified by '패스워드' with grant option;

ERROR 1045 : access denied for user : 'root@localhost'; (using password: no)

원인 : root 패스워드가 설정되어 있는데, root 패스워드 없이 접근을 시도했을 때 나타나는 오류입니다.

해결 : 패스워드를 올바르게 입력해서 접속한다.

mysql -u root (X) mysql -u root -p (O) 패스워드 입력 후 접근

ERROR 1006 : Can't create database 'abcdb'. (errno: 28)

원인 : 하드디스크 파티션 용량이 꽉 참.

해결 : 하드디스크 용량 정리

하드 디스크 용량 정리 팁
우선 순위인 /tmp 디렉토리 안에 데이터 먼저 제거 한다.
cd /tmp
rm -rf *
service mysqld restart

ERROR 1175 : You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences

원인 : Safe Update 모드 상태로 설정되어 있어서 UPDATE시 KEY 칼럼을 이용하지 않을 경우 업데이트할 수 없음.

해결 : Safe Update 모드 해제 명령어 실행

해결 예시 :  SET SQL_SAFE_UPDATES =0;

위에 해당 사항이 없는 경우
MySQL 시스템 문제 에러 로그 보는 법

(tail or more or vi) /etc/log/mysqld.log 혹은 /var/log/mysqld.log

SQL 오류

SQL 오류 (1054) : Unknown column 'sss' in 'where clause'

원인 : where 문에 문법이 잘못됐을 때

원인 발생 예시 : select * from table where col1 = sss

해결 : where 절에 문자열 일 경우에 따옴표로 비교해야 합니다.

해결 예시 select * from table where col1 = 'sss'

SQL 오류 (1061) : Duplicate key name 'abcidx'

원인 : 'abcidx' 인덱스 이름이 중복될 경우

해결 : 인덱스 이름을 변경해준다.

SQL 오류 (1062) : Duplicate entry '1' for key 1

원인 : PK(Primary key)가 걸려있는 칼럼에 이미 있는 데이터를 동일하게 넣을 경우

원인 발생 예시 : '1'이라는 숫자 데이터가 있는데 또 '1' 을 넣을 경우

해결 : 이미 있는 데이터를 피해서 다른 값의 데이터를 넣는다.

SQL 오류 (1064) : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'card_company WHERE amount_of_payment = ss' at line 1

원인 : SQL 문법이 틀렸을 경우 (진짜 많이 보는 오류 내용)

해결 : 틀린 SQL 문법을 찾는다. 끝에 line n번째라고 적혀있는데 이 라인을 잘 봐야 합니다.

SQL 오류 (1072) : Key column 'abc' doesn't exist in table

원인 : 'abc' 란 칼럼이 존재하지 않는데, 'abc'로 인덱스를 생성할 경우

해결 : 존재하는 칼럼에 인덱스를 부여한다.

SQL 오류 (1093) : Table 'TESTNAME' is specified twice, both as a target for 'INSERT' and as a separate source for data

원인 : 하나의 SQL 구문에 같은 테이블 명을 사용할 경우. (INSERT, UPDATE, DELETE)

원인 발생 예시 : INSERT INTO TESTNAME (ID,NAME,ORDER_NO)
VALUES (1,'Java119',( SELECT IFNULL(MAX(ORDER_NO), 0) + 1 FROM TESTNAME WHERE ID = 1))

해결 : 서브 쿼리 부분에 별칭(alias)을 설정해준다.

해결 예시 : INSERT INTO TESTNAME (ID,NAME,ORDER_NO) 
VALUES (1,'Java119',( SELECT IFNULL(MAX(ORDER_NO), 0) + 1 FROM TESTNAME AS tableAlias WHERE ID = 1))

SQL 오류 (1136) : Column count doesn't match value count at row 1

원인 : INSERT문이 테이블 열의 수와 값의 수가 일치하지 않을 경우

원인 발생 예시 : INSERT INTO people (id,NAME,age) VALUES(1,'java119');

해결 : INSERT문 사용 시 테이블 열의 수와 값의 수를 일치시킨다.

해결 예시 : INSERT INTO people (id,NAME,age) VALUES(1,'java119',24);

SQL 오류 (1222) : The used SELECT statements have a different number of columns

원인 : union 사용 시 각 테이블의 칼럼이 서로 일치하지 않는 경우

원인 발생 예시 :

SELECT id,name FROM people 
UNION  
SELECT id FROM people2

해결 : union 사용 테이블들의 칼럼 수를 맞춰준다.

해결 예시 :

SELECT id FROM people  
UNION   
SELECT id FROM people2

SQL 오류 (1242) : Subquery returns more than 1 row

원인 : 서브 쿼리가 하나 이상의 레코드를 리턴하는 문장들에 대해 발생하는 에러

해결 : 서브 쿼리의 결과가 하나의 레코드를 반환하도록 문법 수정

해결 02 : ANY 사용해 해결한 케이스 https://ra2kstar.tistory.com/52

이 글은 계속 업데이트됩니다.

해결법이 올바르지 않거나, 다른 방법으로 해결하신 분들은 피드백 남겨주시면 정말 감사하겠습니다.^^

그 외 오류 참고 자료

MariaDB 오류 코드 https://mariadb.com/kb/en/mariadb-error-codes/

그 외 오류들 (스왑)

MySQL Error Codes
Error 1000 - SQLSTATE: HY000 (ER_HASHCHK) hashchk

Error 1001 - SQLSTATE: HY000 (ER_NISAMCHK) isamchk

Error 1002 - SQLSTATE: HY000 (ER_NO) NO

Error 1003 - SQLSTATE: HY000 (ER_YES) YES

Error 1004 - SQLSTATE: HY000 (ER_CANT_CREATE_FILE) Can't create file '%s' (errno: %d)

Error 1005 - SQLSTATE: HY000 (ER_CANT_CREATE_TABLE) Can't create table '%s' (errno: %d)

Error 1006 - SQLSTATE: HY000 (ER_CANT_CREATE_DB) Can't create database '%s' (errno: %d)

Error 1007 - SQLSTATE: HY000 (ER_DB_CREATE_EXISTS) Can't create database '%s'; database exists

Error 1008 - SQLSTATE: HY000 (ER_DB_DROP_EXISTS) Can't drop database '%s'; database doesn't exist

Error 1009 - SQLSTATE: HY000 (ER_DB_DROP_DELETE) Error dropping database (can't delete '%s', errno: %d)

Error 1010 - SQLSTATE: HY000 (ER_DB_DROP_RMDIR) Error dropping database (can't rmdir '%s', errno: %d)

Error 1011 - SQLSTATE: HY000 (ER_CANT_DELETE_FILE) Error on delete of '%s' (errno: %d)

Error 1012 - SQLSTATE: HY000 (ER_CANT_FIND_SYSTEM_REC) Can't read record in system table

Error 1013 - SQLSTATE: HY000 (ER_CANT_GET_STAT) Can't get status of '%s' (errno: %d)

Error 1014 - SQLSTATE: HY000 (ER_CANT_GET_WD) Can't get working directory (errno: %d)

Error 1015 - SQLSTATE: HY000 (ER_CANT_LOCK) Can't lock file (errno: %d)

Error 1016 - SQLSTATE: HY000 (ER_CANT_OPEN_FILE) Can't open file: '%s' (errno: %d)

Error 1017 - SQLSTATE: HY000 (ER_FILE_NOT_FOUND) Can't find file: '%s' (errno: %d)

Error 1018 - SQLSTATE: HY000 (ER_CANT_READ_DIR) Can't read dir of '%s' (errno: %d)

Error 1019 - SQLSTATE: HY000 (ER_CANT_SET_WD) Can't change dir to '%s' (errno: %d)

Error 1020 - SQLSTATE: HY000 (ER_CHECKREAD) Record has changed since last read in table '%s'

Error 1021 - SQLSTATE: HY000 (ER_DISK_FULL) Disk full (%s); waiting for someone to free some space...

Error 1022 - SQLSTATE: 23000 (ER_DUP_KEY) Can't write; duplicate key in table '%s'

Error 1023 - SQLSTATE: HY000 (ER_ERROR_ON_CLOSE) Error on close of '%s' (errno: %d)

Error 1024 - SQLSTATE: HY000 (ER_ERROR_ON_READ) Error reading file '%s' (errno: %d)

Error 1025 - SQLSTATE: HY000 (ER_ERROR_ON_RENAME) Error on rename of '%s' to '%s' (errno: %d)

Error 1026 - SQLSTATE: HY000 (ER_ERROR_ON_WRITE) Error writing file '%s' (errno: %d)

Error 1027 - SQLSTATE: HY000 (ER_FILE_USED) '%s' is locked against change

Error 1028 - SQLSTATE: HY000 (ER_FILSORT_ABORT) Sort aborted

Error 1029 - SQLSTATE: HY000 (ER_FORM_NOT_FOUND) View '%s' doesn't exist for '%s'

Error 1030 - SQLSTATE: HY000 (ER_GET_ERRNO) Got error %d from storage engine

Error 1031 - SQLSTATE: HY000 (ER_ILLEGAL_HA) Table storage engine for '%s' doesn't have this option

Error 1032 - SQLSTATE: HY000 (ER_KEY_NOT_FOUND) Can't find record in '%s'

Error 1033 - SQLSTATE: HY000 (ER_NOT_FORM_FILE) Incorrect information in file: '%s'

Error 1034 - SQLSTATE: HY000 (ER_NOT_KEYFILE) Incorrect key file for table '%s'; try to repair it

Error 1035 - SQLSTATE: HY000 (ER_OLD_KEYFILE) Old key file for table '%s'; repair it!

Error 1036 - SQLSTATE: HY000 (ER_OPEN_AS_READONLY) Table '%s' is read only

Error 1037 - SQLSTATE: HY001 (ER_OUTOFMEMORY) Out of memory; restart server and try again (needed %d bytes)

Error 1038 - SQLSTATE: HY001 (ER_OUT_OF_SORTMEMORY) Out of sort memory; increase server sort buffer size

Error 1039 - SQLSTATE: HY000 (ER_UNEXPECTED_EOF) Unexpected EOF found when reading file '%s' (errno: %d)

Error 1040 - SQLSTATE: 08004 (ER_CON_COUNT_ERROR) Too many connections

Error 1041 - SQLSTATE: HY000 (ER_OUT_OF_RESOURCES) Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

Error 1042 - SQLSTATE: 08S01 (ER_BAD_HOST_ERROR) Can't get hostname for your address

Error 1043 - SQLSTATE: 08S01 (ER_HANDSHAKE_ERROR) Bad handshake

Error 1044 - SQLSTATE: 42000 (ER_DBACCESS_DENIED_ERROR) Access denied for user '%s'@'%s' to database '%s'

Error 1045 - SQLSTATE: 28000 (ER_ACCESS_DENIED_ERROR) Access denied for user '%s'@'%s' (using password: %s)

Error 1046 - SQLSTATE: 3D000 (ER_NO_DB_ERROR) No database selected

Error 1047 - SQLSTATE: 08S01 (ER_UNKNOWN_COM_ERROR) Unknown command

Error 1048 - SQLSTATE: 23000 (ER_BAD_NULL_ERROR) Column '%s' cannot be null

Error 1049 - SQLSTATE: 42000 (ER_BAD_DB_ERROR) Unknown database '%s'

Error 1050 - SQLSTATE: 42S01 (ER_TABLE_EXISTS_ERROR) Table '%s' already exists

Error 1051 - SQLSTATE: 42S02 (ER_BAD_TABLE_ERROR) Unknown table '%s'

Error 1052 - SQLSTATE: 23000 (ER_NON_UNIQ_ERROR) Column '%s' in %s is ambiguous

Error 1053 - SQLSTATE: 08S01 (ER_SERVER_SHUTDOWN) Server shutdown in progress

Error 1054 - SQLSTATE: 42S22 (ER_BAD_FIELD_ERROR) Unknown column '%s' in '%s'

Error 1055 - SQLSTATE: 42000 (ER_WRONG_FIELD_WITH_GROUP) '%s' isn't in GROUP BY

Error 1056 - SQLSTATE: 42000 (ER_WRONG_GROUP_FIELD) Can't group on '%s'

Error 1057 - SQLSTATE: 42000 (ER_WRONG_SUM_SELECT) Statement has sum functions and columns in same statement

Error 1058 - SQLSTATE: 21S01 (ER_WRONG_VALUE_COUNT) Column count doesn't match value count

Error 1059 - SQLSTATE: 42000 (ER_TOO_LONG_IDENT) Identifier name '%s' is too long

Error 1060 - SQLSTATE: 42S21 (ER_DUP_FIELDNAME) Duplicate column name '%s'

Error 1061 - SQLSTATE: 42000 (ER_DUP_KEYNAME) Duplicate key name '%s'

Error 1062 - SQLSTATE: 23000 (ER_DUP_ENTRY) Duplicate entry '%s' for key %d

Error 1063 - SQLSTATE: 42000 (ER_WRONG_FIELD_SPEC) Incorrect column specifier for column '%s'

Error 1064 - SQLSTATE: 42000 (ER_PARSE_ERROR) %s near '%s' at line %d

Error 1065 - SQLSTATE: HY000 (ER_EMPTY_QUERY) Query was empty

Error 1066 - SQLSTATE: 42000 (ER_NONUNIQ_TABLE) Not unique table/alias: '%s'

Error 1067 - SQLSTATE: 42000 (ER_INVALID_DEFAULT) Invalid default value for '%s'

Error 1068 - SQLSTATE: 42000 (ER_MULTIPLE_PRI_KEY) Multiple primary key defined

Error 1069 - SQLSTATE: 42000 (ER_TOO_MANY_KEYS) Too many keys specified; max %d keys allowed

Error 1070 - SQLSTATE: 42000 (ER_TOO_MANY_KEY_PARTS) Too many key parts specified; max %d parts allowed

Error 1071 - SQLSTATE: 42000 (ER_TOO_LONG_KEY) Specified key was too long; max key length is %d bytes

Error 1072 - SQLSTATE: 42000 (ER_KEY_COLUMN_DOES_NOT_EXITS) Key column '%s' doesn't exist in table

Error 1073 - SQLSTATE: 42000 (ER_BLOB_USED_AS_KEY) BLOB column '%s' can't be used in key specification with the used table type

Error 1074 - SQLSTATE: 42000 (ER_TOO_BIG_FIELDLENGTH) Column length too big for column '%s' (max = %d); use BLOB instead

Error 1075 - SQLSTATE: 42000 (ER_WRONG_AUTO_KEY) Incorrect table definition; there can be only one auto column and it must be defined as a key

Error 1076 - SQLSTATE: HY000 (ER_READY) %s: ready for connections. Version: '%s' socket: '%s' port: %d

Error 1077 - SQLSTATE: HY000 (ER_NORMAL_SHUTDOWN) %s: Normal shutdown

Error 1078 - SQLSTATE: HY000 (ER_GOT_SIGNAL) %s: Got signal %d. Aborting!

Error 1079 - SQLSTATE: HY000 (ER_SHUTDOWN_COMPLETE) %s: Shutdown complete

Error 1080 - SQLSTATE: 08S01 (ER_FORCING_CLOSE) %s: Forcing close of thread %ld user: '%s'

Error 1081 - SQLSTATE: 08S01 (ER_IPSOCK_ERROR) Can't create IP socket

Error 1082 - SQLSTATE: 42S12 (ER_NO_SUCH_INDEX) Table '%s' has no index like the one used in CREATE INDEX; recreate the table

Error 1083 - SQLSTATE: 42000 (ER_WRONG_FIELD_TERMINATORS) Field separator argument is not what is expected; check the manual

Error 1084 - SQLSTATE: 42000 (ER_BLOBS_AND_NO_TERMINATED) You can't use fixed rowlength with BLOBs; please use 'fields terminated by'

Error 1085 - SQLSTATE: HY000 (ER_TEXTFILE_NOT_READABLE) The file '%s' must be in the database directory or be readable by all

Error 1086 - SQLSTATE: HY000 (ER_FILE_EXISTS_ERROR) File '%s' already exists

Error 1087 - SQLSTATE: HY000 (ER_LOAD_INFO) Records: %ld Deleted: %ld Skipped: %ld Warnings: %ld

Error 1088 - SQLSTATE: HY000 (ER_ALTER_INFO) Records: %ld Duplicates: %ld

Error 1089 - SQLSTATE: HY000 (ER_WRONG_SUB_KEY) Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys

Error 1090 - SQLSTATE: 42000 (ER_CANT_REMOVE_ALL_FIELDS) You can't delete all columns with ALTER TABLE; use DROP TABLE instead

Error 1091 - SQLSTATE: 42000 (ER_CANT_DROP_FIELD_OR_KEY) Can't DROP '%s'; check that column/key exists

Error 1092 - SQLSTATE: HY000 (ER_INSERT_INFO) Records: %ld Duplicates: %ld Warnings: %ld

Error 1093 - SQLSTATE: HY000 (ER_UPDATE_TABLE_USED) You can't specify target table '%s' for update in FROM clause

Error 1094 - SQLSTATE: HY000 (ER_NO_SUCH_THREAD) Unknown thread id: %lu

Error 1095 - SQLSTATE: HY000 (ER_KILL_DENIED_ERROR) You are not owner of thread %lu

Error 1096 - SQLSTATE: HY000 (ER_NO_TABLES_USED) No tables used

Error 1097 - SQLSTATE: HY000 (ER_TOO_BIG_SET) Too many strings for column %s and SET

Error 1098 - SQLSTATE: HY000 (ER_NO_UNIQUE_LOGFILE) Can't generate a unique log-filename %s.(1-999)

Error 1099 - SQLSTATE: HY000 (ER_TABLE_NOT_LOCKED_FOR_WRITE) Table '%s' was locked with a READ lock and can't be updated

Error 1100 - SQLSTATE: HY000 (ER_TABLE_NOT_LOCKED) Table '%s' was not locked with LOCK TABLES

Error 1101 - SQLSTATE: 42000 (ER_BLOB_CANT_HAVE_DEFAULT) BLOB/TEXT column '%s' can't have a default value

Error 1102 - SQLSTATE: 42000 (ER_WRONG_DB_NAME) Incorrect database name '%s'

Error 1103 - SQLSTATE: 42000 (ER_WRONG_TABLE_NAME) Incorrect table name '%s'

Error 1104 - SQLSTATE: 42000 (ER_TOO_BIG_SELECT) The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

Error 1105 - SQLSTATE: HY000 (ER_UNKNOWN_ERROR) Unknown error

Error 1106 - SQLSTATE: 42000 (ER_UNKNOWN_PROCEDURE) Unknown procedure '%s'

Error 1107 - SQLSTATE: 42000 (ER_WRONG_PARAMCOUNT_TO_PROCEDURE) Incorrect parameter count to procedure '%s'

Error 1108 - SQLSTATE: HY000 (ER_WRONG_PARAMETERS_TO_PROCEDURE) Incorrect parameters to procedure '%s'

Error 1109 - SQLSTATE: 42S02 (ER_UNKNOWN_TABLE) Unknown table '%s' in %s

Error 1110 - SQLSTATE: 42000 (ER_FIELD_SPECIFIED_TWICE) Column '%s' specified twice

Error 1111 - SQLSTATE: HY000 (ER_INVALID_GROUP_FUNC_USE) Invalid use of group function

Error 1112 - SQLSTATE: 42000 (ER_UNSUPPORTED_EXTENSION) Table '%s' uses an extension that doesn't exist in this MySQL version

Error 1113 - SQLSTATE: 42000 (ER_TABLE_MUST_HAVE_COLUMNS) A table must have at least 1 column

Error 1114 - SQLSTATE: HY000 (ER_RECORD_FILE_FULL) The table '%s' is full

Error 1115 - SQLSTATE: 42000 (ER_UNKNOWN_CHARACTER_SET) Unknown character set: '%s'

Error 1116 - SQLSTATE: HY000 (ER_TOO_MANY_TABLES) Too many tables; MySQL can only use %d tables in a join

Error 1117 - SQLSTATE: HY000 (ER_TOO_MANY_FIELDS) Too many columns

Error 1118 - SQLSTATE: 42000 (ER_TOO_BIG_ROWSIZE) Row size too large. The maximum row size for the used table type, not counting BLOBs, is %ld. You have to change some columns to TEXT or BLOBs

Error 1119 - SQLSTATE: HY000 (ER_STACK_OVERRUN) Thread stack overrun: Used: %ld of a %ld stack. Use 'mysqld -O thread_stack=#' to specify a bigger stack if needed

Error 1120 - SQLSTATE: 42000 (ER_WRONG_OUTER_JOIN) Cross dependency found in OUTER JOIN; examine your ON conditions

Error 1121 - SQLSTATE: 42000 (ER_NULL_COLUMN_IN_INDEX) Column '%s' is used with UNIQUE or INDEX but is not defined as NOT NULL

Error 1122 - SQLSTATE: HY000 (ER_CANT_FIND_UDF) Can't load function '%s'

Error 1123 - SQLSTATE: HY000 (ER_CANT_INITIALIZE_UDF) Can't initialize function '%s'; %s

Error 1124 - SQLSTATE: HY000 (ER_UDF_NO_PATHS) No paths allowed for shared library

Error 1125 - SQLSTATE: HY000 (ER_UDF_EXISTS) Function '%s' already exists

Error 1126 - SQLSTATE: HY000 (ER_CANT_OPEN_LIBRARY) Can't open shared library '%s' (errno: %d %s)

Error 1127 - SQLSTATE: HY000 (ER_CANT_FIND_DL_ENTRY) Can't find function '%s' in library'

Error 1128 - SQLSTATE: HY000 (ER_FUNCTION_NOT_DEFINED) Function '%s' is not defined

Error 1129 - SQLSTATE: HY000 (ER_HOST_IS_BLOCKED) Host '%s' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Error 1130 - SQLSTATE: HY000 (ER_HOST_NOT_PRIVILEGED) Host '%s' is not allowed to connect to this MySQL server

Error 1131 - SQLSTATE: 42000 (ER_PASSWORD_ANONYMOUS_USER) You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords

Error 1132 - SQLSTATE: 42000 (ER_PASSWORD_NOT_ALLOWED) You must have privileges to update tables in the mysql database to be able to change passwords for others

Error 1133 - SQLSTATE: 42000 (ER_PASSWORD_NO_MATCH) Can't find any matching row in the user table

Error 1134 - SQLSTATE: HY000 (ER_UPDATE_INFO) Rows matched: %ld Changed: %ld Warnings: %ld

Error 1135 - SQLSTATE: HY000 (ER_CANT_CREATE_THREAD) Can't create a new thread (errno %d); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

Error 1136 - SQLSTATE: 21S01 (ER_WRONG_VALUE_COUNT_ON_ROW) Column count doesn't match value count at row %ld

Error 1137 - SQLSTATE: HY000 (ER_CANT_REOPEN_TABLE) Can't reopen table: '%s'

Error 1138 - SQLSTATE: 42000 (ER_INVALID_USE_OF_NULL) Invalid use of NULL value

Error 1139 - SQLSTATE: 42000 (ER_REGEXP_ERROR) Got error '%s' from regexp

Error 1140 - SQLSTATE: 42000 (ER_MIX_OF_GROUP_FUNC_AND_FIELDS) Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Error 1141 - SQLSTATE: 42000 (ER_NONEXISTING_GRANT) There is no such grant defined for user '%s' on host '%s'

Error 1142 - SQLSTATE: 42000 (ER_TABLEACCESS_DENIED_ERROR) %s command denied to user '%s'@'%s' for table '%s'

Error 1143 - SQLSTATE: 42000 (ER_COLUMNACCESS_DENIED_ERROR) %s command denied to user '%s'@'%s' for column '%s' in table '%s'

Error 1144 - SQLSTATE: 42000 (ER_ILLEGAL_GRANT_FOR_TABLE) Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used

Error 1145 - SQLSTATE: 42000 (ER_GRANT_WRONG_HOST_OR_USER) The host or user argument to GRANT is too long

Error 1146 - SQLSTATE: 42S02 (ER_NO_SUCH_TABLE) Table '%s.%s' doesn't exist

Error 1147 - SQLSTATE: 42000 (ER_NONEXISTING_TABLE_GRANT) There is no such grant defined for user '%s' on host '%s' on table '%s'

Error 1148 - SQLSTATE: 42000 (ER_NOT_ALLOWED_COMMAND) The used command is not allowed with this MySQL version

Error 1149 - SQLSTATE: 42000 (ER_SYNTAX_ERROR) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

Error 1150 - SQLSTATE: HY000 (ER_DELAYED_CANT_CHANGE_LOCK) Delayed insert thread couldn't get requested lock for table %s

Error 1151 - SQLSTATE: HY000 (ER_TOO_MANY_DELAYED_THREADS) Too many delayed threads in use

Error 1152 - SQLSTATE: 08S01 (ER_ABORTING_CONNECTION) Aborted connection %ld to db: '%s' user: '%s' (%s)

Error 1153 - SQLSTATE: 08S01 (ER_NET_PACKET_TOO_LARGE) Got a packet bigger than 'max_allowed_packet' bytes

Error 1154 - SQLSTATE: 08S01 (ER_NET_READ_ERROR_FROM_PIPE) Got a read error from the connection pipe

Error 1155 - SQLSTATE: 08S01 (ER_NET_FCNTL_ERROR) Got an error from fcntl()

Error 1156 - SQLSTATE: 08S01 (ER_NET_PACKETS_OUT_OF_ORDER) Got packets out of order

Error 1157 - SQLSTATE: 08S01 (ER_NET_UNCOMPRESS_ERROR) Couldn't uncompress communication packet

Error 1158 - SQLSTATE: 08S01 (ER_NET_READ_ERROR) Got an error reading communication packets

Error 1159 - SQLSTATE: 08S01 (ER_NET_READ_INTERRUPTED) Got timeout reading communication packets

Error 1160 - SQLSTATE: 08S01 (ER_NET_ERROR_ON_WRITE) Got an error writing communication packets

Error 1161 - SQLSTATE: 08S01 (ER_NET_WRITE_INTERRUPTED) Got timeout writing communication packets

Error 1162 - SQLSTATE: 42000 (ER_TOO_LONG_STRING) Result string is longer than 'max_allowed_packet' bytes

Error 1163 - SQLSTATE: 42000 (ER_TABLE_CANT_HANDLE_BLOB) The used table type doesn't support BLOB/TEXT columns

Error 1164 - SQLSTATE: 42000 (ER_TABLE_CANT_HANDLE_AUTO_INCREMENT) The used table type doesn't support AUTO_INCREMENT columns

Error 1165 - SQLSTATE: HY000 (ER_DELAYED_INSERT_TABLE_LOCKED) INSERT DELAYED can't be used with table '%s' because it is locked with LOCK TABLES

Error 1166 - SQLSTATE: 42000 (ER_WRONG_COLUMN_NAME) Incorrect column name '%s'

Error 1167 - SQLSTATE: 42000 (ER_WRONG_KEY_COLUMN) The used storage engine can't index column '%s'

Error 1168 - SQLSTATE: HY000 (ER_WRONG_MRG_TABLE) All tables in the MERGE table are not identically defined

Error 1169 - SQLSTATE: 23000 (ER_DUP_UNIQUE) Can't write, because of unique constraint, to table '%s'

Error 1170 - SQLSTATE: 42000 (ER_BLOB_KEY_WITHOUT_LENGTH) BLOB/TEXT column '%s' used in key specification without a key length

Error 1171 - SQLSTATE: 42000 (ER_PRIMARY_CANT_HAVE_NULL) All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

Error 1172 - SQLSTATE: 42000 (ER_TOO_MANY_ROWS) Result consisted of more than one row

Error 1173 - SQLSTATE: 42000 (ER_REQUIRES_PRIMARY_KEY) This table type requires a primary key

Error 1174 - SQLSTATE: HY000 (ER_NO_RAID_COMPILED) This version of MySQL is not compiled with RAID support

Error 1175 - SQLSTATE: HY000 (ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE) You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

Error 1176 - SQLSTATE: HY000 (ER_KEY_DOES_NOT_EXITS) Key '%s' doesn't exist in table '%s'

Error 1177 - SQLSTATE: 42000 (ER_CHECK_NO_SUCH_TABLE) Can't open table

Error 1178 - SQLSTATE: 42000 (ER_CHECK_NOT_IMPLEMENTED) The storage engine for the table doesn't support %s

Error 1179 - SQLSTATE: 25000 (ER_CANT_DO_THIS_DURING_AN_TRANSACTION) You are not allowed to execute this command in a transaction

Error 1180 - SQLSTATE: HY000 (ER_ERROR_DURING_COMMIT) Got error %d during COMMIT

Error 1181 - SQLSTATE: HY000 (ER_ERROR_DURING_ROLLBACK) Got error %d during ROLLBACK

Error 1182 - SQLSTATE: HY000 (ER_ERROR_DURING_FLUSH_LOGS) Got error %d during FLUSH_LOGS

Error 1183 - SQLSTATE: HY000 (ER_ERROR_DURING_CHECKPOINT) Got error %d during CHECKPOINT

Error 1184 - SQLSTATE: 08S01 (ER_NEW_ABORTING_CONNECTION) Aborted connection %ld to db: '%s' user: '%s' host: `%s' (%s)

Error 1185 - SQLSTATE: HY000 (ER_DUMP_NOT_IMPLEMENTED) The storage engine for the table does not support binary table dump

Error 1186 - SQLSTATE: HY000 (ER_FLUSH_MASTER_BINLOG_CLOSED) Binlog closed, cannot RESET MASTER

Error 1187 - SQLSTATE: HY000 (ER_INDEX_REBUILD) Failed rebuilding the index of dumped table '%s'

Error 1188 - SQLSTATE: HY000 (ER_MASTER) Error from master: '%s'

Error 1189 - SQLSTATE: 08S01 (ER_MASTER_NET_READ) Net error reading from master

Error 1190 - SQLSTATE: 08S01 (ER_MASTER_NET_WRITE) Net error writing to master

Error 1191 - SQLSTATE: HY000 (ER_FT_MATCHING_KEY_NOT_FOUND) Can't find FULLTEXT index matching the column list

Error 1192 - SQLSTATE: HY000 (ER_LOCK_OR_ACTIVE_TRANSACTION) Can't execute the given command because you have active locked tables or an active transaction

Error 1193 - SQLSTATE: HY000 (ER_UNKNOWN_SYSTEM_VARIABLE) Unknown system variable '%s'

Error 1194 - SQLSTATE: HY000 (ER_CRASHED_ON_USAGE) Table '%s' is marked as crashed and should be repaired

Error 1195 - SQLSTATE: HY000 (ER_CRASHED_ON_REPAIR) Table '%s' is marked as crashed and last (automatic?) repair failed

Error 1196 - SQLSTATE: HY000 (ER_WARNING_NOT_COMPLETE_ROLLBACK) Some non-transactional changed tables couldn't be rolled back

Error 1197 - SQLSTATE: HY000 (ER_TRANS_CACHE_FULL) Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

Error 1198 - SQLSTATE: HY000 (ER_SLAVE_MUST_STOP) This operation cannot be performed with a running slave; run STOP SLAVE first

Error 1199 - SQLSTATE: HY000 (ER_SLAVE_NOT_RUNNING) This operation requires a running slave; configure slave and do START SLAVE

Error 1200 - SQLSTATE: HY000 (ER_BAD_SLAVE) The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Error 1201 - SQLSTATE: HY000 (ER_MASTER_INFO) Could not initialize master info structure; more error messages can be found in the MySQL error log

Error 1202 - SQLSTATE: HY000 (ER_SLAVE_THREAD) Could not create slave thread; check system resources

Error 1203 - SQLSTATE: 42000 (ER_TOO_MANY_USER_CONNECTIONS) User %s has already more than 'max_user_connections' active connections

Error 1204 - SQLSTATE: HY000 (ER_SET_CONSTANTS_ONLY) You may only use constant expressions with SET

Error 1205 - SQLSTATE: HY000 (ER_LOCK_WAIT_TIMEOUT) Lock wait timeout exceeded; try restarting transaction

Error 1206 - SQLSTATE: HY000 (ER_LOCK_TABLE_FULL) The total number of locks exceeds the lock table size

Error 1207 - SQLSTATE: 25000 (ER_READ_ONLY_TRANSACTION) Update locks cannot be acquired during a READ UNCOMMITTED transaction

Error 1208 - SQLSTATE: HY000 (ER_DROP_DB_WITH_READ_LOCK) DROP DATABASE not allowed while thread is holding global read lock

Error 1209 - SQLSTATE: HY000 (ER_CREATE_DB_WITH_READ_LOCK) CREATE DATABASE not allowed while thread is holding global read lock

Error 1210 - SQLSTATE: HY000 (ER_WRONG_ARGUMENTS) Incorrect arguments to %s

Error 1211 - SQLSTATE: 42000 (ER_NO_PERMISSION_TO_CREATE_USER) '%s'@'%s' is not allowed to create new users

Error 1212 - SQLSTATE: HY000 (ER_UNION_TABLES_IN_DIFFERENT_DIR) Incorrect table definition; all MERGE tables must be in the same database

Error 1213 - SQLSTATE: 40001 (ER_LOCK_DEADLOCK) Deadlock found when trying to get lock; try restarting transaction

Error 1214 - SQLSTATE: HY000 (ER_TABLE_CANT_HANDLE_FT) The used table type doesn't support FULLTEXT indexes

Error 1215 - SQLSTATE: HY000 (ER_CANNOT_ADD_FOREIGN) Cannot add foreign key constraint

Error 1216 - SQLSTATE: 23000 (ER_NO_REFERENCED_ROW) Cannot add or update a child row: a foreign key constraint fails

Error 1217 - SQLSTATE: 23000 (ER_ROW_IS_REFERENCED) Cannot delete or update a parent row: a foreign key constraint fails

Error 1218 - SQLSTATE: 08S01 (ER_CONNECT_TO_MASTER) Error connecting to master: %s

Error 1219 - SQLSTATE: HY000 (ER_QUERY_ON_MASTER) Error running query on master: %s

Error 1220 - SQLSTATE: HY000 (ER_ERROR_WHEN_EXECUTING_COMMAND) Error when executing command %s: %s

Error 1221 - SQLSTATE: HY000 (ER_WRONG_USAGE) Incorrect usage of %s and %s

Error 1222 - SQLSTATE: 21000 (ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT) The used SELECT statements have a different number of columns

Error 1223 - SQLSTATE: HY000 (ER_CANT_UPDATE_WITH_READLOCK) Can't execute the query because you have a conflicting read lock

Error 1224 - SQLSTATE: HY000 (ER_MIXING_NOT_ALLOWED) Mixing of transactional and non-transactional tables is disabled

Error 1225 - SQLSTATE: HY000 (ER_DUP_ARGUMENT) Option '%s' used twice in statement

Error 1226 - SQLSTATE: 42000 (ER_USER_LIMIT_REACHED) User '%s' has exceeded the '%s' resource (current value: %ld)

Error 1227 - SQLSTATE: HY000 (ER_SPECIFIC_ACCESS_DENIED_ERROR) Access denied; you need the %s privilege for this operation

Error 1228 - SQLSTATE: HY000 (ER_LOCAL_VARIABLE) Variable '%s' is a SESSION variable and can't be used with SET GLOBAL

Error 1229 - SQLSTATE: HY000 (ER_GLOBAL_VARIABLE) Variable '%s' is a GLOBAL variable and should be set with SET GLOBAL

Error 1230 - SQLSTATE: 42000 (ER_NO_DEFAULT) Variable '%s' doesn't have a default value

Error 1231 - SQLSTATE: 42000 (ER_WRONG_VALUE_FOR_VAR) Variable '%s' can't be set to the value of '%s'

Error 1232 - SQLSTATE: 42000 (ER_WRONG_TYPE_FOR_VAR) Incorrect argument type to variable '%s'

Error 1233 - SQLSTATE: HY000 (ER_VAR_CANT_BE_READ) Variable '%s' can only be set, not read

Error 1234 - SQLSTATE: 42000 (ER_CANT_USE_OPTION_HERE) Incorrect usage/placement of '%s'

Error 1235 - SQLSTATE: 42000 (ER_NOT_SUPPORTED_YET) This version of MySQL doesn't yet support '%s'

Error 1236 - SQLSTATE: HY000 (ER_MASTER_FATAL_ERROR_READING_BINLOG) Got fatal error %d: '%s' from master when reading data from binary log

Error 1237 - SQLSTATE: HY000 (ER_SLAVE_IGNORED_TABLE) Slave SQL thread ignored the query because of replicate-*-table rules

Error 1238 - SQLSTATE: HY000 (ER_INCORRECT_GLOBAL_LOCAL_VAR) Variable '%s' is a %s variable

Error 1239 - SQLSTATE: 42000 (ER_WRONG_FK_DEF) Incorrect foreign key definition for '%s': %s

Error 1240 - SQLSTATE: HY000 (ER_KEY_REF_DO_NOT_MATCH_TABLE_REF) Key reference and table reference don't match

Error 1241 - SQLSTATE: 21000 (ER_OPERAND_COLUMNS) Operand should contain %d column(s)

Error 1242 - SQLSTATE: 21000 (ER_SUBQUERY_NO_1_ROW) Subquery returns more than 1 row

Error 1243 - SQLSTATE: HY000 (ER_UNKNOWN_STMT_HANDLER) Unknown prepared statement handler (%.*s) given to %s

Error 1244 - SQLSTATE: HY000 (ER_CORRUPT_HELP_DB) Help database is corrupt or does not exist

Error 1245 - SQLSTATE: HY000 (ER_CYCLIC_REFERENCE) Cyclic reference on subqueries

Error 1246 - SQLSTATE: HY000 (ER_AUTO_CONVERT) Converting column '%s' from %s to %s

Error 1247 - SQLSTATE: 42S22 (ER_ILLEGAL_REFERENCE) Reference '%s' not supported (%s)

Error 1248 - SQLSTATE: 42000 (ER_DERIVED_MUST_HAVE_ALIAS) Every derived table must have its own alias

Error 1249 - SQLSTATE: 01000 (ER_SELECT_REDUCED) Select %u was reduced during optimization

Error 1250 - SQLSTATE: 42000 (ER_TABLENAME_NOT_ALLOWED_HERE) Table '%s' from one of the SELECTs cannot be used in %s

Error 1251 - SQLSTATE: 08004 (ER_NOT_SUPPORTED_AUTH_MODE) Client does not support authentication protocol requested by server; consider upgrading MySQL client

Error 1252 - SQLSTATE: 42000 (ER_SPATIAL_CANT_HAVE_NULL) All parts of a SPATIAL index must be NOT NULL

Error 1253 - SQLSTATE: 42000 (ER_COLLATION_CHARSET_MISMATCH) COLLATION '%s' is not valid for CHARACTER SET '%s'

Error 1254 - SQLSTATE: HY000 (ER_SLAVE_WAS_RUNNING) Slave is already running

Error 1255 - SQLSTATE: HY000 (ER_SLAVE_WAS_NOT_RUNNING) Slave has already been stopped

Error 1256 - SQLSTATE: HY000 (ER_TOO_BIG_FOR_UNCOMPRESS) Uncompressed data size too large; the maximum size is %d (probably, length of uncompressed data was corrupted)

Error 1257 - SQLSTATE: HY000 (ER_ZLIB_Z_MEM_ERROR) ZLIB: Not enough memory

Error 1258 - SQLSTATE: HY000 (ER_ZLIB_Z_BUF_ERROR) ZLIB: Not enough room in the output buffer (probably, length of uncompressed data was corrupted)

Error 1259 - SQLSTATE: HY000 (ER_ZLIB_Z_DATA_ERROR) ZLIB: Input data corrupted

Error 1260 - SQLSTATE: HY000 (ER_CUT_VALUE_GROUP_CONCAT) %d line(s) were cut by GROUP_CONCAT()

Error 1261 - SQLSTATE: 01000 (ER_WARN_TOO_FEW_RECORDS) Row %ld doesn't contain data for all columns

Error 1262 - SQLSTATE: 01000 (ER_WARN_TOO_MANY_RECORDS) Row %ld was truncated; it contained more data than there were input columns

Error 1263 - SQLSTATE: 01000 (ER_WARN_NULL_TO_NOTNULL) Data truncated; NULL supplied to NOT NULL column '%s' at row %ld

Error 1264 - SQLSTATE: 01000 (ER_WARN_DATA_OUT_OF_RANGE) Data truncated; out of range for column '%s' at row %ld

Error 1265 - SQLSTATE: 01000 (ER_WARN_DATA_TRUNCATED) Data truncated for column '%s' at row %ld

Error 1266 - SQLSTATE: HY000 (ER_WARN_USING_OTHER_HANDLER) Using storage engine %s for table '%s'

Error 1267 - SQLSTATE: HY000 (ER_CANT_AGGREGATE_2COLLATIONS) Illegal mix of collations (%s,%s) and (%s,%s) for operation '%s'

Error 1268 - SQLSTATE: HY000 (ER_DROP_USER) Can't drop one or more of the requested users

Error 1269 - SQLSTATE: HY000 (ER_REVOKE_GRANTS) Can't revoke all privileges, grant for one or more of the requested users

Error 1270 - SQLSTATE: HY000 (ER_CANT_AGGREGATE_3COLLATIONS) Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'

Error 1271 - SQLSTATE: HY000 (ER_CANT_AGGREGATE_NCOLLATIONS) Illegal mix of collations for operation '%s'

Error 1272 - SQLSTATE: HY000 (ER_VARIABLE_IS_NOT_STRUCT) Variable '%s' is not a variable component (can't be used as XXXX.variable_name)

Error 1273 - SQLSTATE: HY000 (ER_UNKNOWN_COLLATION) Unknown collation: '%s'

Error 1274 - SQLSTATE: HY000 (ER_SLAVE_IGNORED_SSL_PARAMS) SSL parameters in CHANGE MASTER are ignored because this MySQL slave was compiled without SSL support; they can be used later if MySQL slave with SSL is started

Error 1275 - SQLSTATE: HY000 (ER_SERVER_IS_IN_SECURE_AUTH_MODE) Server is running in --secure-auth mode, but '%s'@'%s' has a password in the old format; please change the password to the new format

Error 1276 - SQLSTATE: HY000 (ER_WARN_FIELD_RESOLVED) Field or reference '%s%s%s%s%s' of SELECT #%d was resolved in SELECT #%d

Error 1277 - SQLSTATE: HY000 (ER_BAD_SLAVE_UNTIL_COND) Incorrect parameter or combination of parameters for START SLAVE UNTIL

Error 1278 - SQLSTATE: HY000 (ER_MISSING_SKIP_SLAVE) It is recommended to use --skip-slave-start when doing step-by-step replication with START SLAVE UNTIL; otherwise, you will get problems if you get an unexpected slave's mysqld restart

Error 1279 - SQLSTATE: HY000 (ER_UNTIL_COND_IGNORED) SQL thread is not to be started so UNTIL options are ignored

Error 1280 - SQLSTATE: 42000 (ER_WRONG_NAME_FOR_INDEX) Incorrect index name '%s'

Error 1281 - SQLSTATE: 42000 (ER_WRONG_NAME_FOR_CATALOG) Incorrect catalog name '%s'

Error 1282 - SQLSTATE: HY000 (ER_WARN_QC_RESIZE) Query cache failed to set size %lu; new query cache size is %lu

Error 1283 - SQLSTATE: HY000 (ER_BAD_FT_COLUMN) Column '%s' cannot be part of FULLTEXT index

Error 1284 - SQLSTATE: HY000 (ER_UNKNOWN_KEY_CACHE) Unknown key cache '%s'

Error 1285 - SQLSTATE: HY000 (ER_WARN_HOSTNAME_WONT_WORK) MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work

Error 1286 - SQLSTATE: 42000 (ER_UNKNOWN_STORAGE_ENGINE) Unknown table engine '%s'

Error 1287 - SQLSTATE: HY000 (ER_WARN_DEPRECATED_SYNTAX) '%s' is deprecated; use '%s' instead

Error 1288 - SQLSTATE: HY000 (ER_NON_UPDATABLE_TABLE) The target table %s of the %s is not updatable

Error 1289 - SQLSTATE: HY000 (ER_FEATURE_DISABLED) The '%s' feature is disabled; you need MySQL built with '%s' to have it working

Error 1290 - SQLSTATE: HY000 (ER_OPTION_PREVENTS_STATEMENT) The MySQL server is running with the %s option so it cannot execute this statement

Error 1291 - SQLSTATE: HY000 (ER_DUPLICATED_VALUE_IN_TYPE) Column '%s' has duplicated value '%s' in %s

Error 1292 - SQLSTATE: HY000 (ER_TRUNCATED_WRONG_VALUE) Truncated incorrect %s value: '%s'

Error 1293 - SQLSTATE: HY000 (ER_TOO_MUCH_AUTO_TIMESTAMP_COLS) Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Error 1294 - SQLSTATE: HY000 (ER_INVALID_ON_UPDATE) Invalid ON UPDATE clause for '%s' column

Error 1295 - SQLSTATE: HY000 (ER_UNSUPPORTED_PS) This command is not supported in the prepared statement protocol yet

Error 1296 - SQLSTATE: HY000 (ER_GET_ERRMSG) Got error %d '%s' from %s

Error 1297 - SQLSTATE: HY000 (ER_GET_TEMPORARY_ERRMSG) Got temporary error %d '%s' from %s

Error 1298 - SQLSTATE: HY000 (ER_UNKNOWN_TIME_ZONE) Unknown or incorrect time zone: '%s'

Error 1299 - SQLSTATE: HY000 (ER_WARN_INVALID_TIMESTAMP) Invalid TIMESTAMP value in column '%s' at row %ld

Error 1300 - SQLSTATE: HY000 (ER_INVALID_CHARACTER_STRING) Invalid %s character string: '%s'

Error 1301 - SQLSTATE: HY000 (ER_WARN_ALLOWED_PACKET_OVERFLOWED) Result of %s() was larger than max_allowed_packet (%d) - truncated

Error 1302 - SQLSTATE: 2F003 (ER_SP_NO_RECURSIVE_CREATE) Can't create a %s from within another stored routine

Error 1303 - SQLSTATE: 42000 (ER_SP_ALREADY_EXISTS) %s %s already exists

Error 1304 - SQLSTATE: 42000 (ER_SP_DOES_NOT_EXIST) %s %s does not exist

Error 1305 - SQLSTATE: HY000 (ER_SP_DROP_FAILED) Failed to DROP %s %s

Error 1306 - SQLSTATE: HY000 (ER_SP_STORE_FAILED) Failed to CREATE %s %s

Error 1307 - SQLSTATE: 42000 (ER_SP_LILABEL_MISMATCH) %s with no matching label: %s

Error 1308 - SQLSTATE: 42000 (ER_SP_LABEL_REDEFINE) Redefining label %s

Error 1309 - SQLSTATE: 42000 (ER_SP_LABEL_MISMATCH) End-label %s without match

Error 1310 - SQLSTATE: 01000 (ER_SP_UNINIT_VAR) Referring to uninitialized variable %s

Error 1311 - SQLSTATE: 0A000 (ER_SP_BADSELECT) SELECT in a stored procedure must have INTO

Error 1312 - SQLSTATE: 42000 (ER_SP_BADRETURN) RETURN is only allowed in a FUNCTION

Error 1313 - SQLSTATE: 0A000 (ER_SP_BADSTATEMENT) Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION

Error 1314 - SQLSTATE: 42000 (ER_UPDATE_LOG_DEPRECATED_IGNORED) The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been ignored

Error 1315 - SQLSTATE: 42000 (ER_UPDATE_LOG_DEPRECATED_TRANSLATED) The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been translated to SET SQL_LOG_BIN

Error 1316 - SQLSTATE: 70100 (ER_QUERY_INTERRUPTED) Query execution was interrupted

Error 1317 - SQLSTATE: 42000 (ER_SP_WRONG_NO_OF_ARGS) Incorrect number of arguments for %s %s; expected %u, got %u

Error 1318 - SQLSTATE: 42000 (ER_SP_COND_MISMATCH) Undefined CONDITION: %s

Error 1319 - SQLSTATE: 42000 (ER_SP_NORETURN) No RETURN found in FUNCTION %s

Error 1320 - SQLSTATE: 2F005 (ER_SP_NORETURNEND) FUNCTION %s ended without RETURN

Error 1321 - SQLSTATE: 42000 (ER_SP_BAD_CURSOR_QUERY) Cursor statement must be a SELECT

Error 1322 - SQLSTATE: 42000 (ER_SP_BAD_CURSOR_SELECT) Cursor SELECT must not have INTO

Error 1323 - SQLSTATE: 42000 (ER_SP_CURSOR_MISMATCH) Undefined CURSOR: %s

Error 1324 - SQLSTATE: 24000 (ER_SP_CURSOR_ALREADY_OPEN) Cursor is already open

Error 1325 - SQLSTATE: 24000 (ER_SP_CURSOR_NOT_OPEN) Cursor is not open

Error 1326 - SQLSTATE: 42000 (ER_SP_UNDECLARED_VAR) Undeclared variable: %s

Error 1327 - SQLSTATE: HY000 (ER_SP_WRONG_NO_OF_FETCH_ARGS) Incorrect number of FETCH variables

Error 1328 - SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA) No data to FETCH

Error 1329 - SQLSTATE: 42000 (ER_SP_DUP_PARAM) Duplicate parameter: %s

Error 1330 - SQLSTATE: 42000 (ER_SP_DUP_VAR) Duplicate variable: %s

Error 1331 - SQLSTATE: 42000 (ER_SP_DUP_COND) Duplicate condition: %s

Error 1332 - SQLSTATE: 42000 (ER_SP_DUP_CURS) Duplicate cursor: %s

Error 1333 - SQLSTATE: HY000 (ER_SP_CANT_ALTER) Failed to ALTER %s %s

Error 1334 - SQLSTATE: 0A000 (ER_SP_SUBSELECT_NYI) Subselect value not supported

Error 1335 - SQLSTATE: 42000 (ER_SP_NO_USE) USE is not allowed in a stored procedure

Error 1336 - SQLSTATE: 42000 (ER_SP_VARCOND_AFTER_CURSHNDLR) Variable or condition declaration after cursor or handler declaration

Error 1337 - SQLSTATE: 42000 (ER_SP_CURSOR_AFTER_HANDLER) Cursor declaration after handler declaration

Error 1338 - SQLSTATE: 20000 (ER_SP_CASE_NOT_FOUND) Case not found for CASE statement

Error 1339 - SQLSTATE: HY000 (ER_FPARSER_TOO_BIG_FILE) Configuration file '%s' is too big

Error 1340 - SQLSTATE: HY000 (ER_FPARSER_BAD_HEADER) Malformed file type header in file '%s'

Error 1341 - SQLSTATE: HY000 (ER_FPARSER_EOF_IN_COMMENT) Unexpected end of file while parsing comment '%s'

Error 1342 - SQLSTATE: HY000 (ER_FPARSER_ERROR_IN_PARAMETER) Error while parsing parameter '%s' (line: '%s')

Error 1343 - SQLSTATE: HY000 (ER_FPARSER_EOF_IN_UNKNOWN_PARAMETER) Unexpected end of file while skipping unknown parameter '%s'

Error 1344 - SQLSTATE: HY000 (ER_VIEW_NO_EXPLAIN) EXPLAIN/SHOW can not be issued; lacking privileges for underlying table

Error 1345 - SQLSTATE: HY000 (ER_FRM_UNKNOWN_TYPE) File '%s' has unknown type '%s' in its header

Error 1346 - SQLSTATE: HY000 (ER_WRONG_OBJECT) '%s.%s' is not %s

Error 1347 - SQLSTATE: HY000 (ER_NONUPDATEABLE_COLUMN) Column '%s' is not updatable

Error 1348 - SQLSTATE: HY000 (ER_VIEW_SELECT_DERIVED) View's SELECT contains a subquery in the FROM clause

Error 1349 - SQLSTATE: HY000 (ER_VIEW_SELECT_PROCEDURE) View's SELECT contains a PROCEDURE clause

Error 1350 - SQLSTATE: HY000 (ER_VIEW_SELECT_VARIABLE) View's SELECT contains a variable or parameter

Error 1351 - SQLSTATE: HY000 (ER_VIEW_SELECT_TMPTABLE) View's SELECT contains a temporary table '%s'

Error 1352 - SQLSTATE: HY000 (ER_VIEW_WRONG_LIST) View's SELECT and view's field list have different column counts

Error 1353 - SQLSTATE: HY000 (ER_WARN_VIEW_MERGE) View merge algorithm can't be used here for now (assumed undefined algorithm)

Error 1354 - SQLSTATE: HY000 (ER_WARN_VIEW_WITHOUT_KEY) View being updated does not have complete key of underlying table in it

Error 1355 - SQLSTATE: HY000 (ER_VIEW_INVALID) View '%s.%s' references invalid table(s) or column(s)

Error 1356 - SQLSTATE: HY000 (ER_SP_NO_DROP_SP) Can't drop a %s from within another stored routine

Error 1357 - SQLSTATE: HY000 (ER_SP_GOTO_IN_HNDLR) GOTO is not allowed in a stored procedure handler

Error 2000 - (CR_UNKNOWN_ERROR) Unknown MySQL error

Error 2001 - (CR_SOCKET_CREATE_ERROR) Can't create UNIX socket (%d)

Error 2002 - (CR_CONNECTION_ERROR) Can't connect to local MySQL server through socket '%s' (%d)

Error 2003 - (CR_CONN_HOST_ERROR) Can't connect to MySQL server on '%s' (%d)

Error 2004 - (CR_IPSOCK_ERROR) Can't create TCP/IP socket (%d)

Error 2005 - (CR_UNKNOWN_HOST) Unknown MySQL server host '%s' (%d)

Error 2006 - (CR_SERVER_GONE_ERROR) MySQL server has gone away

Error 2007 - (CR_VERSION_ERROR) Protocol mismatch; server version = %d, client version = %d

Error 2008 - (CR_OUT_OF_MEMORY) MySQL client ran out of memory

Error 2009 - (CR_WRONG_HOST_INFO) Wrong host info

Error 2010 - (CR_LOCALHOST_CONNECTION) Localhost via UNIX socket

Error 2011 - (CR_TCP_CONNECTION) %s via TCP/IP

Error 2012 - (CR_SERVER_HANDSHAKE_ERR) Error in server handshake

Error 2013 - (CR_SERVER_LOST) Lost connection to MySQL server during query

Error 2014 - (CR_COMMANDS_OUT_OF_SYNC) Commands out of sync; you can't run this command now

Error 2015 - (CR_NAMEDPIPE_CONNECTION) %s via named pipe

Error 2016 - (CR_NAMEDPIPEWAIT_ERROR) Can't wait for named pipe to host: %s pipe: %s (%lu)

Error 2017 - (CR_NAMEDPIPEOPEN_ERROR) Can't open named pipe to host: %s pipe: %s (%lu)

Error 2018 - (CR_NAMEDPIPESETSTATE_ERROR) Can't set state of named pipe to host: %s pipe: %s (%lu)

Error 2019 - (CR_CANT_READ_CHARSET) Can't initialize character set %s (path: %s)

Error 2020 - (CR_NET_PACKET_TOO_LARGE) Got packet bigger than 'max_allowed_packet' bytes

Error 2021 - (CR_EMBEDDED_CONNECTION) Embedded server

Error 2022 - (CR_PROBE_SLAVE_STATUS) Error on SHOW SLAVE STATUS:

Error 2023 - (CR_PROBE_SLAVE_HOSTS) Error on SHOW SLAVE HOSTS:

Error 2024 - (CR_PROBE_SLAVE_CONNECT) Error connecting to slave:

Error 2025 - (CR_PROBE_MASTER_CONNECT) Error connecting to master:

Error 2026 - (CR_SSL_CONNECTION_ERROR) SSL connection error

Error 2027 - (CR_MALFORMED_PACKET) Malformed packet

Error 2028 - (CR_WRONG_LICENSE) This client library is licensed only for use with MySQL servers having '%s' license

Error 2029 - (CR_NULL_POINTER) Invalid use of null pointer

Error 2030 - (CR_NO_PREPARE_STMT) Statement not prepared

Error 2031 - (CR_PARAMS_NOT_BOUND) No data supplied for parameters in prepared statement

Error 2032 - (CR_DATA_TRUNCATED) Data truncated

Error 2033 - (CR_NO_PARAMETERS_EXISTS) No parameters exist in the statement

Error 2034 - (CR_INVALID_PARAMETER_NO) Invalid parameter number

Error 2035 - (CR_INVALID_BUFFER_USE) Can't send long data for non-string/non-binary data types (parameter: %d)

Error 2036 - (CR_UNSUPPORTED_PARAM_TYPE) Using unsupported buffer type: %d (parameter: %d)

Error 2037 - (CR_SHARED_MEMORY_CONNECTION) Shared memory (%lu)

Error 2038 - (CR_SHARED_MEMORY_CONNECT_REQUEST_ERROR) Can't open shared memory; client could not create request event (%lu)

Error 2039 - (CR_SHARED_MEMORY_CONNECT_ANSWER_ERROR) Can't open shared memory; no answer event received from server (%lu)

Error 2040 - (CR_SHARED_MEMORY_CONNECT_FILE_MAP_ERROR) Can't open shared memory; server could not allocate file mapping (%lu)

Error 2041 - (CR_SHARED_MEMORY_CONNECT_MAP_ERROR) Can't open shared memory; server could not get pointer to file mapping (%lu)

Error 2042 - (CR_SHARED_MEMORY_FILE_MAP_ERROR) Can't open shared memory; client could not allocate file mapping (%lu)

Error 2043 - (CR_SHARED_MEMORY_MAP_ERROR) Can't open shared memory; client could not get pointer to file mapping (%lu)

Error 2044 - (CR_SHARED_MEMORY_EVENT_ERROR) Can't open shared memory; client could not create %s event (%lu)

Error 2045 - (CR_SHARED_MEMORY_CONNECT_ABANDONED_ERROR) Can't open shared memory; no answer from server (%lu)

Error 2046 - (CR_SHARED_MEMORY_CONNECT_SET_ERROR) Can't open shared memory; cannot send request event to server (%lu)

Error 2047 - (CR_CONN_UNKNOW_PROTOCOL) Wrong or unknown protocol

Error 2048 - (CR_INVALID_CONN_HANDLE) Invalid connection handle

Error 2049 - (CR_SECURE_AUTH) Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

Error 2050 - (CR_FETCH_CANCELED) Row retrieval was canceled by mysql_stmt_close() call

Error 2051 - (CR_NO_DATA) Attempt to read column without prior row fetch

저작자표시

'MariaDB' 카테고리의 다른 글

[MySQL \|\| MariaDB] TIMESTAMP와 DATETIME 차이점 총 정리 (0)	2020.04.19
[MySQL \|\| MariaDB] ALTER TABLE 문법 총 정리 (0)	2020.03.22
[MySQL \|\| MariaDB] 숫자형 문자 정렬 문제 및 해결(Feat.varchar) (0)	2020.03.15
[MySQL \|\| MariaDB] 여러 테이블 한번에 카운터 하기 (0)	2020.03.15
[MySQL \|\| MariaDB] ORDER BY 특정 값 우선 정렬(feat.FIELD) (2)	2020.02.29

DATETIME

index : 불가능

time zone : 영향 없음

타입 : 문자형

용량 : 8 byte

지원 범위 : 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

ON UPDATE 구문 : 사용 가능 (사용 불가로 알고 있었는데 테스트해보니 잘 되네요.)

TIMESTAMP

index : 가능

time zone : 영향 있음

타입 : 숫자형

용량 : 4 byte

지원 범위 : 1970-01-01 00:00:00 ~ 2038-01-19 03:14:07

ON UPDATE 구문 : 사용 가능

※ 주의 : 마이크로 초까지 얻으려면 timestamp( 1~6의 값 )을 설정 해줘야 합니다.

설정하지 않을 경우 기본값 0

DATETIME과 TIMESTAMP의 차이점

DATETIME은 입력된 날짜와 시간 그대로 데이터를 저장하지만

TIMESTAMP는 time_zone 시스템 변수로 값을 지정한다.

TIMESTAMP는 데이터 입출력시 time_zone 시스템 변수 값을 체크해 그 기반으로 변환하여 처리한다.

한마디로 가장 큰 차이점은 DATETIME은 일정하며 TIMESTAMP는 time_zone 설정의 영향을 받습니다.

아주 대표적인 예로 보자면

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+

mysql> create table datedemo(
    -> mydatetime datetime,
    -> mytimestamp timestamp
    -> );

mysql> insert into datedemo values ((now()),(now()));

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

mysql> set time_zone="america/new_york";

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

출처 : tech-recipes

결론은 TIMESTAMP는 time_zone 시스템 변수에 따른 값 변화가 크다는 것이다.

그럼 변수가 많으니 무조건 DATETIME을 써야 할까요? 그렇지 않습니다.

해외에 나가는 사이트라면 TIMESTAMP와 DATETIME을 적절히 잘 사용하여야 합니다.

더욱 자세한 걸 원하시는 분은

TIMESTAMP

https://mariadb.com/kb/en/timestamp/

DATETIME

https://mariadb.com/kb/en/datetime/

저작자표시

'MariaDB' 카테고리의 다른 글

[MySQL \|\| MariaDB] System,SQL ERROR 모음 원인 및 해결 총 정리 (0)	2020.04.24
[MySQL \|\| MariaDB] ALTER TABLE 문법 총 정리 (0)	2020.03.22
[MySQL \|\| MariaDB] 숫자형 문자 정렬 문제 및 해결(Feat.varchar) (0)	2020.03.15
[MySQL \|\| MariaDB] 여러 테이블 한번에 카운터 하기 (0)	2020.03.15
[MySQL \|\| MariaDB] ORDER BY 특정 값 우선 정렬(feat.FIELD) (2)	2020.02.29

문법

문법에 들어가기 앞서, 공통 테이블 예시

CREATE TABLE ex_table (
id INT,
sFirst VARCHAR(32),
sThird VARCHAR(32),
nSecond INT,
sFifth VARCHAR(32)
)

컬럼 추가 (Add)

nSixth INT형 컬럼 추가

ALTER TABLE [테이블명] ADD [컬럼명] [타입]
ALTER TABLE ex_table ADD COLUMN nSixth INT;

결과

특정 컬럼 뒤에 추가 (Add)

sThird 컬럼 뒤에 sFourth VARCHAR형 컬럼 추가

ALTER TABLE [테이블명] ADD COLUMN [추가할컬럼명] [컬럼타입] DEFAULT [기본값] [컬럼위치]
ALTER TABLE ex_table ADD COLUMN sFourth varchar(32) DEFAULT NULL AFTER sThird;

결과

컬럼 타입 변경 (Modify)

sFifth 컬럼 VARCHAR(32) -> VARCHAR(55)로 변경

ALTER TABLE [테이블명] MODIFY [컬럼명] [타입]
ALTER TABLE ex_table MODIFY COLUMN sFifth VARCHAR(55);

결과

여러 개 컬럼 한번에 변경 (Modify)

nSixth 컬럼 INT(11) -> INT(6) , sFirst 컬럼 VARCHAR(32) -> VARCHAR(11)

ALTER TABLE ex_table
MODIFY COLUMN nSixth INT(6),
MODIFY COLUMN sFirst VARCHAR(11);

결과

컬럼 순서변경 (Modify)

sFirst 컬럼 뒤에 nSecond 컬럼 배치 하기

ALTER TABLE [테이블명] MODIFY [순서변경 할 컬럼명] [컬럼타입] AFTER [컬럼위치];
ALTER TABLE ex_table MODIFY nSecond int AFTER sFirst;

결과

컬럼 이름까지 변경 (Change)

nSecond 컬럼 이름 변경 -> sSecond , 타입 변경 INT -> VARCHAR(22)

ALTER TABLE [테이블명] CHANGE [변경 전 컬럼명] [변경 후 컬럼명] [컬럼타입]
ALTER TABLE ex_table CHANGE COLUMN nSecond sSecond VARCHAR(22);

결과

주의 : 기존 데이터가 유실될수 있으므로, 컬럼의 크기는 늘릴수만 있습니다.

컬럼 삭제 (Drop)

nSixth 컬럼 삭제

ALTER TABLE [테이블명] DROP [컬럼명]
ALTER TABLE ex_table DROP COLUMN nSixth;

결과

Primary Key(PK) 설정

id 컬럼 Primary Key(PK) 추가

ALTER TABLE [테이블명] ADD PRIMARY KEY (Key 설정 컬럼 명01, Key 설정 컬럼 명02) 
ALTER TABLE ex_table ADD PRIMARY KEY (id);

결과

Primary Key(PK) 삭제

모든 컬럼 Primary Key(PK) 삭제

ALTER TABLE [테이블명] DROP PRIMARY KEY
ALTER TABLE ex_table DROP PRIMARY KEY;

결과

컬럼 디폴트값 변경

sFirst 컬럼 기본 값을 '첫번째값'으로 설정

ALTER TABLE [테이블명] ALTER COLUMN [컬럼명] SET DEFAULT [기본값]
ALTER TABLE ex_table ALTER COLUMN sFirst SET DEFAULT '첫번째값';

결과

컬럼 디폴트값 삭제

sFirst 컬럼 기본 값을 삭제

ALTER TABLE [테이블명] ALTER [컬럼명] DROP DEFAULT
ALTER TABLE ex_table ALTER sFirst DROP DEFAULT;

결과

컬럼 인덱스 설정

id 컬럼 `pkindex` 인덱스 설정

ALTER TABLE [테이블명] ADD INDEX 인덱스명(인덱스 설정 컬럼01, 인덱스 설정 컬럼02) 
ALTER TABLE ex_table ADD INDEX pkindex(id);

결과

컬럼 인덱스 삭제

pkindex 인덱스 삭제

ALTER TABLE [테이블명] DROP INDEX [인덱스명]
ALTER TABLE ex_table DROP INDEX pkindex;

결과

테이블 이름 변경 (RENAME)

테이블 이름 변경 ex_table -> ex_alterTable

ALTER TABLE [테이블명] RENAME [변경 후 테이블명]
ALTER TABLE ex_table RENAME ex_alterTable;

결과

테이블 스토리지 엔진(Storage Engine) 변경

스토리지 엔진 종류 두 가지 : [InnoDB || MyISAM]

ex_alterTable 엔진을 INNODB로 변경

ALTER TABLE [테이블명] ENGINE = [형식]
ALTER TABLE ex_alterTable ENGINE = INNODB

현재 스토리지 엔진 확인 방법

SELECT engine, support FROM information_schema.engines WHERE support='DEFAULT';

예제 파일 제공

[MariaDB] ALTER TABLE.sql

0.00MB

저작자표시

'MariaDB' 카테고리의 다른 글

[MySQL \|\| MariaDB] System,SQL ERROR 모음 원인 및 해결 총 정리 (0)	2020.04.24
[MySQL \|\| MariaDB] TIMESTAMP와 DATETIME 차이점 총 정리 (0)	2020.04.19
[MySQL \|\| MariaDB] 숫자형 문자 정렬 문제 및 해결(Feat.varchar) (0)	2020.03.15
[MySQL \|\| MariaDB] 여러 테이블 한번에 카운터 하기 (0)	2020.03.15
[MySQL \|\| MariaDB] ORDER BY 특정 값 우선 정렬(feat.FIELD) (2)	2020.02.29

개념

숫자(INT)형 문자(varchar)를 기준으로 정렬하게 되면 원하는 대로 되지 않는다.

바아로~ 해결 해봅시다.

문법

문법에 들어가기 앞서, 공통 테이블 예시

CREATE TABLE orderEx (

id INT PRIMARY KEY AUTO_INCREMENT,
varcharId VARCHAR(64),
textId TEXT(64)

)

INSERT INTO orderex VALUES(1,'col1','1');

INSERT INTO orderex (varcharId,textId) VALUES('col2','2');
INSERT INTO orderex (varcharId,textId) VALUES('col3','3');
INSERT INTO orderex (varcharId,textId) VALUES('col4','4');
INSERT INTO orderex (varcharId,textId) VALUES('col5','5');
INSERT INTO orderex (varcharId,textId) VALUES('col6','6');
INSERT INTO orderex (varcharId,textId) VALUES('col7','7');
INSERT INTO orderex (varcharId,textId) VALUES('col8','8');
INSERT INTO orderex (varcharId,textId) VALUES('col9','9');
INSERT INTO orderex (varcharId,textId) VALUES('col10','10');
INSERT INTO orderex (varcharId,textId) VALUES('col11','11');
INSERT INTO orderex (varcharId,textId) VALUES('col12','12');

예시 테이블 orderex

만약 varcharId 컬럼 기준으로 ORDER BY를 하게 되면 결과는 이렇다.

SELECT * FROM orderex ORDER BY varcharId

결과

이와 같이 ~~개 같은 결과~~가 나온다.(물론 textId의 ORDER BY 결과도 동일하다)

해결

정렬(오름차순)

SELECT * FROM orderex ORDER BY varcharId*1

결과

만약 역 정렬하고 싶다면?

역 정렬(내림차순)

SELECT * FROM orderex ORDER BY length(varcharId) desc,varcharId desc

결과

예제 파일 제공

[MariaDB] varchar ORDER BY.sql

0.00MB

저작자표시

'MariaDB' 카테고리의 다른 글

[MySQL \|\| MariaDB] TIMESTAMP와 DATETIME 차이점 총 정리 (0)	2020.04.19
[MySQL \|\| MariaDB] ALTER TABLE 문법 총 정리 (0)	2020.03.22
[MySQL \|\| MariaDB] 여러 테이블 한번에 카운터 하기 (0)	2020.03.15
[MySQL \|\| MariaDB] ORDER BY 특정 값 우선 정렬(feat.FIELD) (2)	2020.02.29
[MySQL \|\| MariaDB] InnoDB 총 정리 (1)	2019.12.28

개념

한 번에 여러 테이블의 값을 추출할 수 있다.

문법

문법에 들어가기 앞서, 공통 테이블 예시

기본 문법

SELECT 테이블별칭.카운트별칭,테이블별칭.카운트별칭,테이블별칭.카운트별칭
     FROM
           ( select count(*) 카운트별칭 from 테이블명 조건(선택) ) 테이블별칭
        ,  ( select count(*) 카운트별칭 from 테이블명) 테이블별칭
        ,  ( select count(*) 카운트별칭 from 테이블명) 테이블별칭

예시 1.board 테이블 중 views(조회수)가 30 이상인 것, members 테이블 중 id가 1인 멤버를 카운트

SELECT a.views, b.beu
FROM (SELECT COUNT(*) views FROM board WHERE views > 30) a , 
	 (SELECT COUNT(*) beu FROM members WHERE id = 1) b

결과

예시 2.board 테이블의 총 조회수, 총 좋아요 수, members 테이블의 총 유저수를 카운트

SELECT a.views_length,a.likes_length, b.user_length
FROM (SELECT SUM(views) views_length,SUM(likes) likes_length FROM board) a , 
	  (SELECT COUNT(*) user_length FROM members) b

결과

자주 나는 오류

/* SQL 오류 (1060): Duplicate column name 'spaceAll' */

SELECT `storage`.storageAll, host.hostAll

FROM

( select sum(allca) spaceAll,sum(useca) spaceAll,sum(space) spaceAll from storageInfo) `storage`

, ( select count(*) hostAll from hostInfo) host

이와 같이 별칭을 중복으로 쓰셔서 그렇습니다.

저작자표시

'MariaDB' 카테고리의 다른 글

[MySQL \|\| MariaDB] ALTER TABLE 문법 총 정리 (0)	2020.03.22
[MySQL \|\| MariaDB] 숫자형 문자 정렬 문제 및 해결(Feat.varchar) (0)	2020.03.15
[MySQL \|\| MariaDB] ORDER BY 특정 값 우선 정렬(feat.FIELD) (2)	2020.02.29
[MySQL \|\| MariaDB] InnoDB 총 정리 (1)	2019.12.28
[MySQL \|\| MariaDB] 데이터베이스 접근 권한 거부 Host 'IP' is not allowed to connect to this MySQL server (1)	2019.11.19

FIELD

개념

FIELD 함수를 이용하여 특정한 값을 우선적으로 정렬할 수 있다.

문법

문법에 들어가기 앞서, 공통 테이블 예시

기본 문법

SELECT * FROM 테이블명 ORDER BY FIELD(컬럼명,우선 정렬할 값,두번째 정렬할 값,세번째...);

예시 1.제목(subject) 컬럼에 값이 '제목3'이 제일 먼저,'제목5'가 그다음으로 들어간 row 먼저 정렬

SELECT subject FROM board ORDER BY FIELD(subject,'제목3','제목5'),subject;

결과

'제목3' 값을 가진 row가 첫 번째, '제목5'을 가진 row가 두 번째로 정렬됐고 그다음 subject 컬럼이 정렬됐다.

예시 2.제목(subject) 컬럼에 값이 '제목4'이 들어간 row 먼저 정렬하고, 나머진 제목을 기준으로 역 정렬 하기

SELECT subject FROM board ORDER BY FIELD(subject,'제목4'),subject DESC;

결과

특별 예시. 값의 인덱스 찾기(해당 값이 없을 경우 0 반환)

SELECT FIELD(subject,'제목1','제목2') FROM board;

결과

저작자표시

'MariaDB' 카테고리의 다른 글

[MySQL \|\| MariaDB] 숫자형 문자 정렬 문제 및 해결(Feat.varchar) (0)	2020.03.15
[MySQL \|\| MariaDB] 여러 테이블 한번에 카운터 하기 (0)	2020.03.15
[MySQL \|\| MariaDB] InnoDB 총 정리 (1)	2019.12.28
[MySQL \|\| MariaDB] 데이터베이스 접근 권한 거부 Host 'IP' is not allowed to connect to this MySQL server (1)	2019.11.19
[MySQL \|\| MariaDB] 데이터 파일 IMPORT 하기(Feat.LOAD DATA INFILE) (0)	2019.11.15

InnoDB

개념

Transaction-safe 한 Storage Engine으로 2005년 Oracle이 Innobase를 인수한 후 Oracle의 일부가 되었으며,

MySQL 5.5 이후 기본적으로 사용되고 있다. MyISAM 보다 많은 기능을 제공한다.

(MVCC 지원, ACID Transaction 처리, FK 지원, row-level lock 등)

또한 Undo, Tablespace 등 Oracle의 개념을 많이 수용하고 있다.

사용 정책의 경우 듀얼 라이선스 정책을 취하고 있는데 GNU GPL이면서도 상업적인 용도로 판매 가능하다.

MyISAM 대비 동시처리에 효과적인 구조로 되어 있다.

예를 들어 MyISAM은 Table과 Index를 각각 다른 파일로 관리하는데 반해 InnoDB는 Tablespace 개념을 사용한다.

사용 이유

대용량의 데이터를 컨트롤하는 경우

트랜잭션 관리가 필요한 경우

복구가 필요할 경우

정렬등의 구문이 들어가는 경우

IUD 등이 빈번하게 발생하는 경우

높은 퍼포먼스가 필요한 대용량 사이트에 적합

장점

데이터 무결성이 보장

동시성 제어가 가능

제약조건, 외래 키 생성이 가능

Row-level Lock (행 단위 Lock)을 사용하기 때문에 변경 작업(INSERT, UPDATE, DELETE)에 대한 속도가 빠름

트랜잭션을 지원해 transaction-safe 테이블 관리

MyISAM과 비슷하지만 ORACLE처럼 많은 기능을 지원

(Commit, Rollback, 장애 복구, row-level locking, 외래 키 등 다양한 기능을 지원)

단점

복구 방법 어려움

Dead lock 발생 가능성 있음

여러기능이 존재하므로 모델 디자인 시간↑

시스템 자원을 많이 차지함

Full-text 인덱싱이 불가능

생성 조건 및 지켜야 할 수칙

InnoDB table 생성시 체크할 항목

1. 테이블의 column은 1000개를 초과할 수 없다.

2. 내부 최대 Key length는 3500byte이지만, MySQL 자체는 1024byte로 제한한다

3. LongBlob 및 LongText 칼럼은 4GB 이하여야 한다.

4. Blob와 Text 칼럼을 포함한 총 length는 4G 이하여야 한다.

5. InnoDB는 내부적으로 65,535byte row-size를 지원하지만, 65,535 이상의 varchar를 포함한 칼럼은 정의할 수 없다

관련 에러

(ERROR 1114 (HY000): The table 'TBSOJM00' is full? )

원인

MySQL || MariaDB에서 테이블을 만들다 보면 4 G 이상의 크기가 되면 이런 류의 에러를 발생하게 됩니다.

이것은 테이블의 크기가 기본적으로 4GB로 되어 있기 때문에 발생하는 오류

해결 방법

http://blog.daum.net/_blog/BlogTypeView.do?blogid=0FcAa&articleno=12414824&categoryId=548228®dt=20150311175120

관련 에러 2

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

원인

varchar()의 MAX_ROWS 설정값을 초과한 경우에 발생하는 경우가 많습니다.

저는 DB의 데이터를 다른 서버로 이전할 경우에 종종 발생합니다. 서버별로 DB의 설정이 달라서 발생하는 것 같습니다.

해결 방법

1.MAX_ROWS 값을 늘린다.

해결 : https://blog.lovetonight.net/tag/MAX_ROWS

2.varchar로 선언된 컬럼을 text 또는 blob 로 변경한다.

해결 : 말 그대로 컬럼 타입을 변경하시면 됩니다.

varchar의 자리가 65,535가 안 되는 것 같은데 왜 에러가 났지? 하시는 분들도 있을 겁니다.

그 이유는 바로 utf8 멀티 바이트를 쓰고 계시는 거죠

utf8는 3byte씩 차지하니까 계산기에 65535/3 하시면 됩니다.

귀찮으시죠? 제가 대신했습니다.

21,845가 나옵니다. 안전하게 utf8 멀티 바이트 테이블에 varchar는 21,800까지만 쓰시면 되겠습니다.

저작자표시

'MariaDB' 카테고리의 다른 글

[MySQL \|\| MariaDB] 여러 테이블 한번에 카운터 하기 (0)	2020.03.15
[MySQL \|\| MariaDB] ORDER BY 특정 값 우선 정렬(feat.FIELD) (2)	2020.02.29
[MySQL \|\| MariaDB] 데이터베이스 접근 권한 거부 Host 'IP' is not allowed to connect to this MySQL server (1)	2019.11.19
[MySQL \|\| MariaDB] 데이터 파일 IMPORT 하기(Feat.LOAD DATA INFILE) (0)	2019.11.15
[MySQL \|\| MariaDB] 자주 쓰는 날짜 API와 날짜 실전 예제 총 정리 (1)	2019.11.12

Host '172.10.0.254' is not allowed to connect to this MySQL server

원인

DB를 외부에서 접속 시도시 나는 에러이다.

보안상 root 계정에 아무나 접근 할 수 없기 때문에 허용 할 IP를 지정해줘야 한다.

DB 접속 후 현재 설정 확인.

select Host,User,plugin,authentication_string FROM mysql.user;

모든 IP 허용

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '패스워드';

IP 대역대 허용

GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.10.0.%' IDENTIFIED BY '패스워드';

특정 IP 허용

GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.10.0.254' IDENTIFIED BY '패스워드';

IP 허용 후 원래 상태로 되돌리기

DELETE FROM mysql.user WHERE Host='%' AND User='아이디';
FLUSH PRIVILEGES;

이 설정을 했음에도 불구하고 접근 권한 거부가 뜬다면,

firewall(방화벽)를 확인 해보자. 3306 port를 허용해줘야 한다.

방화벽 포트 확인

firewall-cmd --list-all-zones

3306 포트 허용하기

firewall-cmd --permanent --zone=public --add-port=3306/tcp

저작자표시

'MariaDB' 카테고리의 다른 글

[MySQL \|\| MariaDB] ORDER BY 특정 값 우선 정렬(feat.FIELD) (2)	2020.02.29
[MySQL \|\| MariaDB] InnoDB 총 정리 (1)	2019.12.28
[MySQL \|\| MariaDB] 데이터 파일 IMPORT 하기(Feat.LOAD DATA INFILE) (0)	2019.11.15
[MySQL \|\| MariaDB] 자주 쓰는 날짜 API와 날짜 실전 예제 총 정리 (1)	2019.11.12
[MySQL \|\| MariaDB] 서브쿼리(Subquery) 총 정리 (1)	2019.11.12

.java의 개발일기

MariaDB

[MySQL || MariaDB] System,SQL ERROR 모음 원인 및 해결 총 정리

System 오류

SQL 오류

'MariaDB' 카테고리의 다른 글

[MySQL || MariaDB] TIMESTAMP와 DATETIME 차이점 총 정리

DATETIME

TIMESTAMP

'MariaDB' 카테고리의 다른 글

[MySQL || MariaDB] ALTER TABLE 문법 총 정리

'MariaDB' 카테고리의 다른 글

[MySQL || MariaDB] 숫자형 문자 정렬 문제 및 해결(Feat.varchar)

'MariaDB' 카테고리의 다른 글

[MySQL || MariaDB] 여러 테이블 한번에 카운터 하기

'MariaDB' 카테고리의 다른 글

[MySQL || MariaDB] ORDER BY 특정 값 우선 정렬(feat.FIELD)

FIELD

'MariaDB' 카테고리의 다른 글

[MySQL || MariaDB] InnoDB 총 정리

InnoDB

'MariaDB' 카테고리의 다른 글

[MySQL || MariaDB] 데이터베이스 접근 권한 거부 Host 'IP' is not allowed to connect to this MySQL server

'MariaDB' 카테고리의 다른 글

+ Recent posts

티스토리툴바