4

I recently duplicated an existing MariaDB table (for use on Amazon RDS). The two tables are basically identical, data included, but I noticed the new version was missing all the unique constraints (kept the primary keys just fine). When I tried to add back the uniques, I got an error:

BLOB/TEXT column 'url' used in key specification without a key length

Others have had this problem, and people have claimed that MySQL/MariaDB simply doesn't let you make text columns unique. But my original MariaDB database (on my local machine) has plenty of text columns with unique keys, so that can't be it. I tried their solution anyway (switching to varchar) but MariaDB wouldn't let me do that either because my data has too many characters. Any ideas? Many thanks.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
codi6
  • 516
  • 1
  • 3
  • 18
  • do both servers have the same version of MySQL installed? if they are different it's possible that one version allows that but the other doesn't – Gharbad The Weak Mar 19 '20 at 21:46
  • One is running on my local machine (that one works) and one is on Amazon RDS (which doesn't work) – codi6 Mar 19 '20 at 21:48
  • yeah, what is the version of MySQL locally vs the version of MySQL running on AWS? run `SELECT VERSION();` on each database and compare what is returned – Gharbad The Weak Mar 19 '20 at 21:49
  • Ah, they are different: Local: 10.4.11-MariaDB. Amazon RDS: 10.2.21-MariaDB-log. Hmmmm so what do I do about that? Can I update Amazon's version, or change the tables to work with the older MariaDB, or something? – codi6 Mar 19 '20 at 21:52
  • well, you could change the version that doesn't work to be the same as the version that does work, that might help. I don't know where you got the instruction on changing it to VARCHAR, but looking at this link says the VARCHAR solution might not work if you didn't set the key length properly. https://techjourney.net/mysql-error-1170-42000-blobtext-column-used-in-key-specification-without-a-key-length/ – Gharbad The Weak Mar 19 '20 at 21:54
  • generally i would think that trying to set a unique key constraint on a TEXT column would not be the best thing to do. if your table has any size to it your indexes would get pretty big pretty quick – Gharbad The Weak Mar 19 '20 at 21:56
  • in thinking about it, changing the version of MySQL to a version that allows it would be kind of like painting yourself into a corner. in versions going forward they might not allow that so you wouldn't be able to update your MySQL version. I'd look for another solution other than changing the MySQL version, that link i posted in my previous comment has several suggestions. – Gharbad The Weak Mar 19 '20 at 22:00
  • @codi6 Can you add the query that you're using to add the index? – WOUNDEDStevenJones Mar 19 '20 at 22:22
  • I'm using dBeaver – codi6 Mar 19 '20 at 22:27

1 Answers1

5

MariaDB 10.4 supports a UNIQUE KEY on a TEXT column because it automatically converts the index to a HASH index:

mysql> create table t (id serial primary key, t text, unique key(t));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `t` (`t`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Unfortunately, Amazon RDS for MariaDB does not support MariaDB 10.4 yet (as of 2020-03-19), according to https://aws.amazon.com/rds/mariadb/:

Amazon RDS supports MariaDB Server versions 10.0, 10.1, 10.2, and 10.3 which means that the code, applications, and tools you already use today can be used with Amazon RDS.

The HASH index feature is not supported in MariaDB 10.3 or earlier:

mysql> create table t (id serial primary key, t text, unique key(t));
ERROR 1170 (42000): BLOB/TEXT column 't' used in key specification without a key length

You can create a unique key on a prefix of the text column:

mysql> create table t (id serial primary key, t text, unique key(t(1000)));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `t` (`t`(1000))
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This means the unique key will enforce uniqueness in the first 1000 characters. You won't be allowed to store two rows with string that have the same first 1000 characters, but differ in the 1001st character or later.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • That sounds promising! A little confused though: It doesn't seem like I should be creating so much as altering tables, right? I've been using a database tool (dBeaver) so I'm not super familiar with the Mariadb syntax. – codi6 Mar 19 '20 at 22:35
  • You might like to read https://mariadb.com/kb/en/getting-started-with-indexes/#unique-index – Bill Karwin Mar 19 '20 at 22:46
  • That seemed to work! Turns out I had a typo in the syntax. – codi6 Mar 19 '20 at 22:56
  • 1
    @codi6 glad to hear Bill Karwin's answer worked. You marked his answer as accepted but if I were you I would upvote his answer as well. – Gharbad The Weak Mar 20 '20 at 14:48
  • @BillKarwin - Do you have a reference in the manual for this new 10.4 feature? I failed to find it in the manual and changelogs. – Rick James Mar 29 '20 at 03:42
  • @RickJames I don't have a reference. I looked, but I couldn't find it in the manual or the release notes. That was strange. I just ran a docker container for 10.3 and then for 10.4 and verified the results. – Bill Karwin Mar 29 '20 at 04:48
  • A stealth feature! – Rick James Mar 29 '20 at 19:18