Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL REGEXP fails with error 1139: repetition-operator operand invalid

ErrorERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp

Short 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.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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');
  2. 2Match what you see, then jump to its fix

    1. 1The pattern starts with *, + or ?, or one follows (, | or another operator, for example '*.txt' or '%*'A repetition operator has nothing before itFix
    2. 2The query works for most values and fails when the value contains characters like + ? * ( or {User input with special characters is used as the patternFix

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.