MySQL error: key specification without a key length

The error happens because MySQL can index only the first N chars of a BLOB or TEXT column. So The error mainly happens when there is a field/column type of TEXT or BLOB or those belong to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try to make a primary key or index. With full BLOB or TEXT without the length value, MySQL is unable to guarantee the uniqueness of the column as it’s of variable and dynamic size. So, when using BLOB or TEXT types as an index, the value of N must be supplied so that MySQL can determine the key length. However, MySQL doesn’t support a key length limit on TEXT or BLOB. TEXT(88) simply won’t work.

The error will also pop up when you try to convert a table column from non-TEXT and non-BLOB type such as VARCHAR and ENUM into TEXT or BLOB type, with the column already been defined as unique constraints or index. The Alter Table SQL command will fail.

The solution to the problem is to remove the TEXT or BLOB column from the index or unique constraint or set another field as primary key. If you can’t do that, and wanting to place a limit on the TEXT or BLOB column, try to use VARCHAR type and place a limit of length on it. By default, VARCHAR is limited to a maximum of 255 characters and its limit must be specified implicitly within a bracket right after its declaration, i.e VARCHAR(200) will limit it to 200 characters long only.

Sometimes, even though you don’t use TEXT or BLOB related type in your table, the Error 1170 may also appear. It happens in a situation such as when you specify VARCHAR column as primary key, but wrongly set its length or characters size. VARCHAR can only accepts up to 256 characters, so anything such as VARCHAR(512) will force MySQL to auto-convert the VARCHAR(512) to a SMALLTEXT datatype, which subsequently fails with error 1170 on key length if the column is used as primary key or unique or non-unique index. To solve this problem, specify a figure less than 256 as the size for VARCHAR field.

Reference: MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length

Related Posts:

  1. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  2. Unknown column in ‘field list’ error on MySQL Update query
  3. Unknown column in ‘field list’ error on MySQL Update query
  4. MySQL – Operand should contain 1 column(s)
  5. MySQL “CREATE TABLE IF NOT EXISTS” -> Error 1050
  6. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  7. MySQL create table if not exists and insert record only if table was created
  8. How do I use properly CASE..WHEN in MySQL
  9. How can I do a FULL OUTER JOIN in MySQL?
  10. MySQL query String contains
  11. What’s the difference between VARCHAR and CHAR?
  12. Case statement in MySQL
  13. How to declare a variable in MySQL?
  14. MySQL Cannot Add Foreign Key Constraint
  15. How to split the name string in mysql?
  16. How do I import an SQL file using the command line in MySQL?
  17. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  18. mysql Foreign key constraint is incorrectly formed error
  19. MySQL: Invalid use of group function
  20. How can I return pivot table output in MySQL?
  21. Compare dates in MySQL
  22. MySQL “WITH” clause
  23. MySQL Multiple Joins in one query?
  24. Error Code: 2013. Lost connection to MySQL server during query
  25. MySQL syntax for Join Update
  26. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  27. how to drop partition without dropping data in MySQL?
  28. MySQL combine two columns into one column
  29. Simple way to calculate median with MySQL
  30. Error 1046 No database Selected, how to resolve?
  31. MySQL Error 1264: out of range value for column
  32. Remove duplicate rows in MySQL
  33. How to SUM and SUBTRACT using SQL?
  34. Cast from VARCHAR to INT – MySQL
  35. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  36. How do you force mysql LIKE to be case sensitive?
  37. MySQL – UPDATE multiple rows with different values in one query
  38. How to create a MySQL hierarchical recursive query?
  39. ERROR 1148: The used command is not allowed with this MySQL version
  40. Insert into a MySQL table or update if exists
  41. How to round down to nearest integer in MySQL?
  42. MySQL starts with searching issue
  43. Query to convert from datetime to date mysql
  44. Is it possible to GROUP BY multiple columns using MySQL?
  45. Best way to test if a row exists in a MySQL table
  46. Check if MySQL table exists without using “select from” syntax?
  47. MySQL equivalent of DECODE function in Oracle
  48. mysql update column with value from another table
  49. The used SELECT statements have a different number of columns
  50. How to delete from multiple tables in MySQL?
  51. MySQL: Can’t create table (errno: 150)
  52. How to copy a row and insert in same table with a autoincrement field in MySQL?
  53. Join vs. sub-query
  54. Alternative to except in MySQL
  55. Equivalent of explode() to work with strings in MySQL
  56. ROW_NUMBER() in MySQ
  57. MySQL OPTIMIZE all tables?
  58. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  59. MySQL – Get row number on select
  60. MySQL error: Unknown column in ‘where clause’
  61. How to truncate the text returned for a column in a MySQL query
  62. Using union and order by clause in mysql
  63. MySQL LIKE IN()?
  64. How to create Temp table with SELECT * INTO tempTable FROM CTE Query
  65. Using group by on multiple columns
  66. Efficiently convert rows to columns in sql server
  67. Nested select statement in SQL Server
  68. SQL – HAVING vs. WHERE
  69. What’s the difference between RANK() and DENSE_RANK() functions in oracle?
  70. How to SUM two fields within an SQL query
  71. DateTime2 vs DateTime in SQL Server
  72. Difference between numeric, float and decimal in SQL Server
  73. How can I do ‘insert if not exists’ in MySQL?
  74. “ORA-01438: value larger than specified precision allowed for this column” when inserting 3
  75. How can I prevent SQL injection in PHP?
  76. How can I do ‘insert if not exists’ in MySQL?
  77. Conversion failed when converting the varchar value ‘simple, ‘ to data type int
  78. postgresql: error duplicate key value violates unique constraint
  79. Is there a workaround for ORA-01795: maximum number of expressions in a list is 1000 error?
  80. Convert timestamp to date in Oracle SQL
  81. SQL: Two select statements in one query
  82. What does Include() do in LINQ?
  83. SQL Server “cannot perform an aggregate function on an expression containing an aggregate or a subquery”, but Sybase can
  84. Get everything after and before certain character in SQL Server
  85. SQLite string contains other string query
  86. “#1067 – Invalid default value for ‘post_date'” when trying to reset AI after backup
  87. Inserting Post Meta From SQL
  88. WordPress SQL LIKE request doesn’t work for fields with special symbols
  89. SQL command to convert all tags in lowercase?
  90. How can I add a new row in a separate database when someone registers via WordPress?
  91. WordPress can I manually add columns in users table and display it in the default manage user plugin and registration form? [closed]
  92. $wpdb->get_var – What value is returned when zero records are found?
  93. Why is my insert row only inserting the final row from the loop into the database rather than just inserting one
  94. Database SQL query error
  95. Inserting other fields to existing registration form in a WordPress theme
  96. Organising and completing posts (mark as read and hide)
  97. can’t delete a row from post_meta table
  98. SQL error on restoring database
  99. Creating a “forum” – showing last post or last commented post
  100. If meta_key exists then don’t select this post?

Leave a Comment

tech