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

would be nice to have a small snippet. I've tryed : <?php $dbh = new PDO('mysql:host=localhost;port=3308;dbname=testj', 'root', '');

$dbh->query('CREATE TEMPORARY TABLE ttt (t1 VARCHAR(256), t2 VARCHAR(256))'); $dbh->query("INSERT INTO ttt VALUE ('Oxford & Middlesex', 'Nissouri Twp1E&W')");

$stmt = $dbh->prepare("SELECT COUNT(*) FROM ttt WHERE LOCATE(:district1, `t1`) > 0 AND `t2` REGEXP :area1"); if ($stmt->execute(array ( 'district1' => 'Oxford & Middlesex', 'area1' => 'Nissouri Twp.E&W'))) { while ($row = $stmt->fetch()) { print_r($row); } }

$dbh = null; ?> without issue

Thank you for your followup. In my implementation all of the SQL is in a small number of classes which provide an object-oriented wrapper that is used by the application code. There is only one piece of code that handles ALL queries that might match multiple records, in a class called RecordSet. The class is documented at https://github.com/jcobban/Genealogy/wiki/class-RecordSet where you can also see the source code. The point is that same piece of code is executed for many different requests and this particular query is the only one that has failed out of literally millions of different queries in the over 10 years that the code has been in use. The failure occurs on the request to determine how many records there will be in the response, and again on the query to actually obtain the records:

$baptisms = new RecordSet('MethodistBaptisms',array('district' => 'Oxford & Middlesex', "area" => 'Nissouri Twp.E&W'));

Processing the parameters to the constructor of RecordSet sets the following members of the class: $this->sqlParms = array('district1' => 'Oxford & Middlesex', "area1" => 'Nissouri Twp.E&W'); $this->where = ''WHERE LOCATE(:district1, `district`) > 0 AND `area` REGEXP :area1'; $countQuery = "SELECT COUNT(*) FROM " . $this->table . ' ' . $this->where; line 675

$stmt = $connection->prepare($countQuery); line 754 $queryText = debugPrepQuery($countQuery, $this->sqlParms); if ($stmt->execute($this->sqlParms)) { success if ($debug) { $warn .= "<p>RecordSet.inc: " . LINE . " query=\"$queryText\"</p>\n"; }

return count in parameter object $row = $stmt->fetch(PDO::FETCH_NUM); $this->information['count'] = $row[0]; } success else { error performing query $this->msg .= "RecordSet::construct: \"$queryText\", " . print_r($stmt->errorInfo(),true); print '<p>' . $this->msg . "</p>\n"; print "<p>'$countQuery', parms=" . print_r($this->sqlParms,true) . "</p>\n"; } error performing query

You can invoke this code and trigger the error by using the following URL:

https://www.jamescobban.net/Ontario/WmbResponse.php?district=Oxford+%26+Middlesex&area=Nissouri+Twp.E%26W

It is critical to note the period "." between "Twp" and "E". It is the presence of that period that triggers the use of REGEXP because it is a special character in regular expressions. As a matter of fact the records that I am looking for actually have a period at that position, but the general purpose class cannot know that. Omit the period or replace that period with a character that does not have a special significance to regular expressions and the search is done with LOCATE, as is done for District. You can see that by removing or changing the period in the test URL The RecordSet class tries to avoid using MySQL/MariaDB specific functionality by emitting standard SQL that emulates the requested pattern whenever possible, but does not implement an emulation for the period character.

Someone will still have to tell me how to perform the trace. Since the web server and the Sql server are on the same host they communicate using a named pipe, not by IP over the internet, so there is no IP traffic for wireshark to look at.

I still do not know how to perform the trace. For example I tried the steps described on https://dbtut.com/index.php/2018/11/15/how-to-get-the-data-packets-between-mysql-client-and-server/

$ sudo tcpdump -nnei any port 8001 -w tmp.pcap [sudo] password for jcobban: tcpdump: listening on any, link-type LINUX_SLL (Linux cooked v1), capture size 262144 bytes

At this point I invoked the script http://localhost/Ontario/WmbResponse.php?district=Oxford+%26+Middlesex&area=Nissouri+Twp.E%26W which duplicated the error.

^C0 packets captured 0 packets received by filter 0 packets dropped by kernel

The error is on requests made from PHP to the MariaDB server, not on requests from the mysql client.

I do not know how to perform a wireshark trace of the MariaDB client server connection. As to the PHP level my development system is running PHP Version 7.3.11-0ubuntu0.19.10.2 with mysqlnd 5.0.12-dev - 20150407 and MariaDB 10.3.20-MariaDB-0ubuntu0.19.10.1. Those are quite current as I am running the latest available release of Ubuntu. As noted in the original post my production system is currently running PHP Version 7.2.24-0ubuntu0.18.04.2 with MySQL 5.7.28-0ubuntu0.18.04.4 which is still quite current. So I am experiencing the problem across a broad range of environments. In both cases PHP and the SQL server are on the same host.