This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-17. For the latest, interactive version please visit https://mariadb.com/kb/.

Do not understand why MariaDB rejected REGEXP with 42000 You have an error in your SQL syntax

When I issue the following prepared statement:

SELECT COUNT(*) FROM MethodistBaptisms WHERE LOCATE(:district1, `district`) > 0 AND `area` REGEXP :area1

with the following parameters:

Array ( 'district1' => 'Oxford & Middlesex', 'area1' => 'Nissouri Twp.E&W')

I get:

Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'REGEXP 'Nissouri Twp.E&W'' at line 1 )

However if I manually perform the insertions and issue the query:

SELECT COUNT(*) FROM MethodistBaptisms WHERE LOCATE('Oxford & Middlesex', `district`) > 0 AND `area` REGEXP 'Nissouri Twp.E&W'

it works. If I change the query to:

SELECT COUNT(*) FROM MethodistBaptisms WHERE district=:district1 AND `area` = :area1

with the same parameters, it also works. So there is something specific to the REGEXP which I do not understand. I also note that I am not receiving the form of the error message that I would get if there was an error in the regular expression.

I have tested this against MariaDB 10.3.20-MariaDB-0ubuntu0.19.10.1 and MySQL 5.7.28-0ubuntu0.18.04.4 and I get the same resu

Answer Answered by Diego Dupin in this comment.

It would be nice to create an issue https://jira.mariadb.org/projects/MDEV/issues/ with trace from wireshark, and php version.

I failed to reproduce the issue with latest php and according pdo driver, the only way to identify the issue is having wireshark log to identify if this is a server issue, connector issue or otherwise

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.