📋

Description

Error 1857 indicates that MariaDB failed to acquire the necessary exclusive lock to create or modify a fulltext index. This typically occurs when other operations are actively using the target table, preventing the required lock from being obtained.

💬

Error Message

Fulltext index creation requires a lock

🔍

Known Causes

3 known causes

⚠️

Active Table Operations

Other data manipulation language (DML) or data definition language (DDL) statements are currently running on the target table.

⚠️

Pending Transactions

An open transaction on the table has not yet been committed or rolled back, holding locks that conflict with the index creation operation.

⚠️

Persistent Application Locks

A connected application or client session is holding a lock on the table, preventing the necessary exclusive lock for fulltext index creation.

🛠️

Solutions

3 solutions available

1. Recreate Table with Fulltext Index medium

Create a new table with the fulltext index and then copy data over.

1

Create a new table with the same schema as your original table, but include the FULLTEXT index definition.

CREATE TABLE your_new_table LIKE your_original_table;
ALTER TABLE your_new_table ADD FULLTEXT(column1, column2, ...);

2

Copy all data from the original table to the new table.

INSERT INTO your_new_table SELECT * FROM your_original_table;

3

Drop the original table.

DROP TABLE your_original_table;

4

Rename the new table to the original table's name.

RENAME TABLE your_new_table TO your_original_table;

2. Add Fulltext Index in Offline Mode easy

Perform the index creation when the database or table is not actively being written to.

1

Stop all write operations to the table for which you are creating the fulltext index. This might involve stopping applications or services that write to the database.

N/A

2

Execute the ALTER TABLE statement to add the FULLTEXT index. This should now succeed without the locking issue as there are no concurrent writes.

ALTER TABLE your_table ADD FULLTEXT(column1, column2, ...);

3

Resume write operations to the table and database.

N/A

3. Increase Lock Wait Timeout easy

Allow more time for the lock to be released during index creation.

1

Temporarily increase the `innodb_lock_wait_timeout` system variable. A value of 300 seconds (5 minutes) is often sufficient, but you may need to adjust this based on your table size and server load.

SET GLOBAL innodb_lock_wait_timeout = 300;

2

Attempt to create the FULLTEXT index again.

ALTER TABLE your_table ADD FULLTEXT(column1, column2, ...);

3

It is recommended to revert `innodb_lock_wait_timeout` to its default value after the operation is complete, or set it to a reasonable value that balances responsiveness and operation success.

SET GLOBAL innodb_lock_wait_timeout = 50; -- Or your desired default value

🔗

Related Errors

5 related errors

MariaDB Error 1857: Fulltext Index Locking Issue