online indexing using mariadb 10.4 and innodb?
Hi,
Is there any way to get an online indexing with mariadb? It seems default is NOT. I created a table testdate(dt date). In one session:
MariaDB [test]> start transaction;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> insert into testdate values ('1900-01-04');
Query OK, 1 row affected (0.000 sec)
So while the transaction is in progress, in another session, I tried to create an index on testdate(dt), with no algorithm, algorithm=inplace, ... It hangs always. Problem: I will be in a 24x7 environment, no way to stop apps to create new indexes. Postgres 11 has it (concurrent index).
Any hint? Thanks.
Answer Answered by Marko Mäkelä in this comment.
At the start and end of an ALTER TABLE or CREATE INDEX or DROP INDEX or similar operation, the table will be locked exclusively, that is, any active transactions that may have accessed the table must be committed or aborted for the operation to continue. It is called ‘online’ ALTER TABLE because during time-consuming operations (creating secondary indexes, or rebuilding the table and all its indexes), concurrent modifications are allowed.
https://mariadb.com/kb/en/library/innodb-online-ddl-overview/#alter-locking-strategies has been updated to make this clearer.
