Migration silently does not run sql statement properly

[filiperochalopes]

OS: MacOS Ventura. Project (docker ready): https://github.com/filiperochalopes/cadastro-procedimentos-fisioterapia/tree/develop.

.

To reproduce:

# Run containers with docker-compose, unfortunatelly I can't share the initial database sql because it has sensitive data
make run
propel migrate

The SQL statement is not working but if I run it in a SQL terminal it runs normaly.

   public function preUp(MigrationManager $manager)
    {

        $pdo = $manager->getAdapterConnection('default');

        $tabela_records = TabelaQuery::create()->find();

        echo "Removendo patentes de fisioterapeutas (tabela)...\n";
        // Removendo patentes e títulos do nome do fisioterapeuta na lista de tabelas
        $sql = "UPDATE tabela SET fisioterapeuta=TRIM(REGEXP_REPLACE(fisioterapeuta, 'GM\\s(\\(S\\)\\s)?|Ten\\.\\s|\\s\\(Estagiári.\\)|CC\\s(\\(S\\)\\s)?|SO\\s|Dr.?\\.\\s', '')) WHERE fisioterapeuta  IS NOT NULL;";
        $stmt = $pdo->prepare($sql);
        $stmt->execute();

And the worse part it fails silently and you won't see any warning or error

[filiperochalopes]

Running on dbeaver returns ok.

[mringler]

If you are not getting an error, it suggests that the conditions are not matched, i.e. the regex needs more backslashes. Is it only the query with the regex or all statements that are not working?

[filiperochalopes]

Understood. But as I showed this query has multiple matches. When I run it on dbeaver it updates precisally 25245 rows as showed in the up comment. I used the same database and I restart the database with initial pool data always when I test. I am studying again PHP PDO because I realized it is not working with all UPDATE queries where I am not using biding values even if the statement does not demands one.

[filiperochalopes]

I also tried debugging with PDO::errorInfo but there is no error showed. It just return 0

[mringler]

Pretty sure your problem comes from the backslashes. You are using double quotes around the regex, PHP will strip those:

"\\s" ---PHP--> "\s" ---MySQL Parser--> <non-printable char denoted by \s> ---> Pattern Matcher

Try using single quotes or doubling the slashes. Or maybe even tripling? Escapes are a mess.

[filiperochalopes]

I tryed single slashes before. I will triple it to test.

[filiperochalopes]

Worked with triple slash :face_with_spiral_eyes: Thanks @mringler