after mariadb upgrade, all of my mroonga fulltext index are disabled

steve3d commented 5 years ago

why this happens? I'm using mariadb 10.2/10.3 they all have this problem.

I'm using wrapper mode to create fulltext index, after any mariadb version upgrade, then all of my mroonga fulltext index are marked disabled, I've googled a lot, but can not find any solution to re-enable these index.

Now, the only solution I have is to manually recreate these fulltext index with SQL command.

why this happens? is there any quick way of reenable these fulltext index?

kenhys commented 5 years ago

Hi, It seems that it is not already known issue, so to know what happened on your side, could you show us about details?

I mean the following information is useful.

Distribution (centos 6/7)
Installed from (source,package,and so on)
Installed version
Upgraded version
Messages during upgrade
The reason why you thought index are disabled (your assumption)

steve3d commented 5 years ago

I’m using Ubuntu 17.10

installed mariadb from official binary repo，plugin also installed from same repo

10.3.11-MariaDB-1:10.3.11+maria~bionic - mariadb.org binary distribution

before upgrade everything works, after upgrade not only this version, this is the third time I have this problem the full text index has a comment said disabled in phpmyadmin, also matching against these any index gives me an error of no full text index

Is there a quick way to find out why this happens?

kenhys commented 5 years ago

Thanks @steve3d

It seems that we need some more hints to find out the reason. Is there log file under /var/lib/mysql/groonga.log or something where? (logging period: before upgrade time to after upgrade time) If there is something wrong, it may be logged in.

(It may be mroonga plugin is disabled, but I'm not sure)

steve3d commented 5 years ago

sorry, this happens few times on my production server, every time it happens, this almost kills me.

so I've moved to mysql 8.0 + ngram.

so I can not help with this.

but it's easy to reproduce the problem, create some table with mroonga wrapper mode, then do these:

do not stop the server
directly upgrade the mariadb with apt upgrade
check the fulltext index, all index are marked with comment disabled

komainu8 commented 5 years ago

Thank you for your information. I'll try to reproduce the problem.

komainu8 commented 5 years ago

I could reproduce this problem. Detail procedure for reproducing it as below.

Operating System: Ubuntu 18.10(cosmic) MariaDB Version: 10.3.11 -> 10.3.12

1. Install of MariaDB

% sudo apt-get install software-properties-common
% sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

We add describe below in /etc/apt/sources.list.d/mariadb.list

deb [arch=amd64] http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/mariadb-10.3.11/repo/ubuntu cosmic main
deb-src http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/mariadb-10.3.11/repo/ubuntu cosmic main

Install of MariaDB 10.3.11 and Mroonga.

% sudo apt update
% sudo apt install mariadb-server-10.3
% sudo apt install mariadb-plugin-mroonga

2. Create table and full text index

After connecting a MariaDB, we execute below commands.

CREATE DATABASE sample;
USE sample;

CREATE TABLE diaries (
   id INT PRIMARY KEY AUTO_INCREMENT,
   content VARCHAR(255),
   FULLTEXT INDEX (content)
 ) ENGINE = Mroonga COMMENT = 'engine "InnoDB"' DEFAULT CHARSET utf8;

INSERT INTO diaries (content) VALUES ("It'll be fine tomorrow.");
INSERT INTO diaries (content) VALUES ("It'll rain tomorrow");
INSERT INTO diaries (content) VALUES ("It's fine today. It'll be fine tomorrow as well.");
INSERT INTO diaries (content) VALUES ("It's fine today. But it'll rain tomorrow.");

\q

Mroonga is wrapper mode.

3. Upgrade a MariaDB

We add describe below in /etc/apt/sources.list.d/mariadb.list

deb [arch=amd64] http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.3/ubuntu cosmic main
deb-src http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.3/ubuntu cosmic main

We upgrade to the MariaDB 10.3.12.

% sudo apt update
% sudo apt upgrade mariadb-server

We reconnect to the MariaDB and confirm full text index.

MariaDB [(none)]> USE sample;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [sample]> SHOW INDEX FROM diaries;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment  | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| diaries |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |          |               |
| diaries |          1 | content  |            1 | content     | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   | disabled |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
2 rows in set (0.000 sec)

steve3d commented 5 years ago

see? this bug is easy to reproduce. but it almost drive me crazy about 3 times.

ottok commented 4 years ago

Where is the deb source? Do you have some postinst scripts that do installs/upgrades maybe and they are misbehaving?

steve3d commented 4 years ago

I’m using official Ubuntu package repo.

mroonga / mroonga