MySQL REGEXP fails with error 1139: repetition-operator operand invalid
ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexpShort answer
Error 1139 means the regular expression library rejected your pattern. For repetition-operator operand invalid, a *, + or ? has nothing in front of it to repeat, usually because a LIKE-style wildcard or an unescaped user value went into REGEXP. Put an atom before the operator, such as .*, or escape the character with two backslashes.
Fix it fast
1Find out which cause you have
Run the pattern on its own so you test the expression, not the table. REGEXP_LIKE returns 1, 0 or an error.
SELECT REGEXP_LIKE('test', '*test');2Match what you see, then jump to its fix
- 1
The pattern starts with *, + or ?, or one follows (, | or another operator, for example '*.txt' or '%*'A repetition operator has nothing before itFix - 2
The query works for most values and fails when the value contains characters like + ? * ( or {User input with special characters is used as the patternFix
- 1
What does the error look like?
ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp#1139 - Got error 'repetition-operator operand invalid' from regexp
Cause 1 of 2
A repetition operator has nothing before it
1Check
In a regular expression, * + ? and {n} repeat the previous atom. A pattern copied from shell or LIKE habits, such as '*.csv', has no atom before the star, so the library refuses it.
SELECT REGEXP_LIKE('file.csv', '*.csv');
SELECT REGEXP_LIKE('file.csv', '.*\\.csv');2Fix
Write what should be repeated: use .* for any run of characters, and escape the dot you mean literally. If you really want the text 'file*', escape the star.
SELECT name FROM files WHERE name REGEXP '.*\\.csv$';3Verify
The query runs and returns the rows you expect.
SELECT REGEXP_LIKE('file.csv', '.*\\.csv$');Confirmed by dev.mysql.com
Cause 2 of 2
User input with special characters is used as the pattern
1Check
Special characters in a pattern must be quoted. MySQL string rules eat one backslash, so you write two: one for the SQL parser and one for the regular expression library. Repeat the failing value alone in REGEXP_LIKE.
SELECT REGEXP_LIKE('1+2', '1+2');
SELECT REGEXP_LIKE('1+2', '1\\+2');2Fix
Escape special characters in the value before it goes in the pattern (in application code), or, for a plain substring search, use LIKE or LOCATE and skip regular expressions. With the NO_BACKSLASH_ESCAPES SQL mode on, a single backslash is enough.
-- the escaped form MySQL documents
SELECT REGEXP_LIKE('1+2', '1\\+2');
-- plain substring match, no regex
SELECT * FROM notes WHERE LOCATE('1+2', body) > 0;3Verify
The value that failed now returns a row.
SELECT REGEXP_LIKE('1+2', '1\\+2');Confirmed by dev.mysql.com
How do I stop it coming back?
- Treat REGEXP patterns as code: never build one from raw user input without escaping it.
- Use LIKE or LOCATE when you only need a substring.
- Test the pattern in REGEXP_LIKE with a sample value before putting it in a query.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix