[MDEV-18061] Silent varchar(255) key size truncation to 250 Created: 2018-12-22  Updated: 2019-01-22  Resolved: 2019-01-22

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Olaf van der Spek Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description    

10.3.11-MariaDB-1 Debian buildd-unstable
 
 
 
MariaDB [st_forum]> create table test (a varchar(255), key(a)) engine = myisam;
 
Query OK, 0 rows affected, 1 warning (0.005 sec)
 
 
 
MariaDB [st_forum]> show create table test;
 
+-------+---------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                        |
 
+-------+---------------------------------------------------------------------------------------------------------------------+
 
| test  | CREATE TABLE `test` (
 
  `a` varchar(255) DEFAULT NULL,
 
  KEY `a` (`a`(250))
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 |
 
+-------+---------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.001 sec)

Update:
OK, not entirely silent apparently.. but warnings are nearly invisible.
varchar(255) is a common type.. why is it no longer possible to create indexes on it?

The warning / error also seems to be inconsistent, in alter table convert charset it appears to be an error.
IMO it should always be an error.

MariaDB [st_forum]> show warnings;  
+---------+------+----------------------------------------------------------+
 
| Level   | Code | Message                                                  |
 
+---------+------+----------------------------------------------------------+
 
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
 
+---------+------+----------------------------------------------------------+
 
1 row in set (0.000 sec)



 Comments   
Comment by Sergei Golubchik [ 2019-01-22 ]

This is expected behavior.

Physical key limitation is (and was for a long time) a 1000 bytes. You use utfmb4 character set, it means four bytes per character. Thus you get 250 characters.

But it's nothing to be concerned of. An index is just a optimization, helping to resolve queries faster. This is why the server allows itself to truncate the index as necessary. In your case you would only notice a difference in performance if you store a huge amount of strings that are completely identical in the first 250 characters and only differ in the last five.

Note, that unique index is a constraint, not just an optimization. The server will never truncate a unique index automatically, because it would've affected the correctness of the data.

Comment by Olaf van der Spek [ 2019-01-22 ]

I certainly wasn't expecting this..

> Note, that unique index is a constraint, not just an optimization.

I have such unique constraints on my varchar(255) columns.. I'm sure others have too. What should we do?

Comment by Sergei Golubchik [ 2019-01-22 ]

Currently they'll abort CREATE TABLE with an error.

There is a task to fix it MDEV-371 (despite the name it's not only for blobs, but for anything that doesn't fit in the key length limit). The feature is already implemented, it went through a couple of code reviews — it has a good chance of being in 10.4.

For older versions... You can reduce the column length to 250, or switch to utf8 (3 bytes per char). Or use InnoDB it has larger limits, if I'm not mistaken.

Generated at Thu Feb 08 08:41:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.