Skip to content

Instantly share code, notes, and snippets.

@milano
Last active August 29, 2015 14:00
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save milano/11240352 to your computer and use it in GitHub Desktop.
mroongaのストレージモードにおいてUNIQUEインデックスでDuplicateエラーが発生する
# uname -a
Linux **** 3.10.34-37.137.amzn1.x86_64 #1 SMP Tue Mar 25 01:00:47 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

# mysql --version
mysql  Ver 14.14 Distrib 5.5.36, for Linux (x86_64) using readline 5.1

# groonga --version
groonga 4.0.1 [linux-gnu,x86_64,utf8,match-escalation-threshold=0,nfkc,mecab,zlib,lzo,epoll]

mysql> show variables like '%mroonga%';
+----------------------------------------+---------------+
| Variable_name                          | Value         |
+----------------------------------------+---------------+
| mroonga_action_on_fulltext_query_error | ERROR_AND_LOG |
| mroonga_database_path_prefix           |               |
| mroonga_default_parser                 | TokenMecab    |
| mroonga_default_wrapper_engine         |               |
| mroonga_dry_write                      | OFF           |
| mroonga_enable_optimization            | ON            |
| mroonga_libgroonga_version             | 4.0.1         |
| mroonga_lock_timeout                   | 10000000      |
| mroonga_log_file                       | groonga.log   |
| mroonga_log_level                      | NOTICE        |
| mroonga_match_escalation_threshold     | 0             |
| mroonga_version                        | 4.01          |
+----------------------------------------+---------------+
12 rows in set (0.00 sec)

ラッパーモードのテーブル

CREATE TABLE `wrapper_have_unique` (
  id int(10) unsigned not null,
  uid varchar(150) not null,
  data text not null,
  primary key (id),
  unique key uid (uid)
) ENGINE=mroonga DEFAULT CHARSET=utf8 COMMENT='ENGINE "InnoDB"';

ストレージモード(UNIQUEあり)のテーブル

CREATE TABLE `storage_have_unique` (
  id int(10) unsigned not null,
  uid varchar(150) not null,
  data text not null,
  primary key (id),
  unique key uid (uid)
) ENGINE=mroonga DEFAULT CHARSET=utf8;

ストレージモード(UNIQUEなし)のテーブル

CREATE TABLE `storage_dont_have_unique` (
  id int(10) unsigned not null,
  uid varchar(150) not null,
  data text not null,
  primary key (id)
) ENGINE=mroonga DEFAULT CHARSET=utf8;

レコードをINSERTして

mysql> insert into wrapper_have_unique values (1, 'id', 'text');
Query OK, 1 row affected (0.01 sec)

mysql> insert into storage_have_unique values (1, 'id', 'text');
Query OK, 1 row affected (0.00 sec)

mysql> insert into storage_dont_have_unique values (1, 'id', 'text');
Query OK, 1 row affected (0.00 sec)

一度消す

mysql> delete from wrapper_have_unique where id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> delete from storage_have_unique where id = 1;
Query OK, 1 row affected (0.01 sec)

mysql> delete from storage_dont_have_unique where id = 1;
Query OK, 1 row affected (0.00 sec)

もう一度insertすると

mysql> insert into wrapper_have_unique values (1, 'id', 'text');
Query OK, 1 row affected (0.05 sec)

mysql> insert into storage_have_unique values (1, 'id', 'text');
ERROR 1062 (23000): Duplicate entry 'id' for key 'uid'

mysql> insert into storage_dont_have_unique values (1, 'id', 'text');
Query OK, 1 row affected (0.00 sec)

ストレージモード(UNIQUEあり)の場合にエラーとなってしまう insertする文字列を変えるとinsertできる

mysql> insert into storage_have_unique values (1, 'id2', 'text');
Query OK, 1 row affected (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment