1

When I executed the same statement on two instances of MariaDB, one got an error and the other didn't.

The statement I executed is:

CREATE TABLE `session_tokens` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `token` blob NOT NULL,
  `inserted_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_tokens_token_index` (`token`) USING HASH,
  KEY `session_tokens_user_id_index` (`user_id`),
  CONSTRAINT `session_tokens_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `login_name` varchar(255) NOT NULL,
  `hashed_password` varchar(255) NOT NULL,
  `inserted_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_login_name_index` (`login_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

Error message displayed:

ERROR 1170 (42000): BLOB/TEXT column 'token' used in key specification without a key length

The versions of the two instances are the same.

mariadb  Ver 15.1 Distrib 10.3.29-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

The OS of the instance with the error is Ubuntu 20.04.2 LTS running on Google Cloud Platform. The OS of the other instance is Debian GNU/Linux 10 (buster) running on the local Docker.

According to the discussions already made, it seems correct that the error occurs.

Please let me know if you have any clues as to why this difference occurs.

Tsutomu
  • 4,848
  • 1
  • 46
  • 68
  • 1
    Can you double-check the version? MariaDB since 10.4.3 allows indexes on blobs. (Actually, even if the docker version shows 10.3., I would expect it to not actually be 10.3. and/or have 10.4. changes downported. Check if there is information about that included for your docker container) – Solarflare Jul 27 '21 at 08:36
  • @Solarflare Ah, you are right! The actual version of the second instance is 10.5.9. I was mistakenly looking at the version number reported by the mariadb client. – Tsutomu Jul 27 '21 at 08:46

0 Answers0