mysqli_connect produces "# error=unknown command (code=17)"

[starinacool]

UPDATE Jun 24 2024

The below is actually not a bug, but a feature request to respond to COM_CHANGE_USER (not supported in Manticore) not with an error, but with an ok message, so the clients don't fail.

Bug Description:

PHP version 7.4

$conn=mysqli_connect('p:127.0.0.1','','','',9309) or die();

The code works and connection is being established, but in query.log:

# error=unknown command (code=17)

Tried to change username, password and db name to nulls. Did not helped.

Previous version of Manticore was fine.

Manticore Search Version:

6.3.0

Operating System Version:

Debian 6.1.90-1 (2024-05-03) x86_64 GNU/Linux

Have you tried the latest development version?

None

Internal Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

- [ ] Implementation completed - [ ] Tests developed - [ ] Documentation updated - [ ] Documentation reviewed - [ ] Changelog updated

[starinacool]

Removing "p:" from the host parameter fixes the problem.

It looks like manticore logging the problem with change_user mysql command which it does not have.

Here is the quote from PHP docs:

Prepending host by p: opens a persistent connection. [mysqli_change_user()](https://www.php.net/manual/en/mysqli.change-user.php) is automatically called on connections opened from the connection pool.

[sanikolaev]

I can't reproduce this with:

snikolaev@dev2:~$ php -v
PHP 8.1.28 (cli) (built: Apr 22 2024 09:45:11) (NTS)

and

snikolaev@dev2:~$ searchd -v
Manticore 6.3.1 00e77610d@24052911 dev (columnar 2.3.1 4383a90@24052309) (secondary 2.3.1 4383a90@24052309) (knn 2.3.1 4383a90@24052309)

snikolaev@dev2:~$ cat test_persistent.php
<?php
$conn=mysqli_connect('p:127.0.0.1','','','',9306) or die();
$res = mysqli_query($conn, "select 1");
while ($row = $res->fetch_assoc()) var_dump($row);

snikolaev@dev2:~$ php test_persistent.php
array(1) {
  [1]=>
  string(1) "1"
}

snikolaev@dev2:~$ sudo tail /var/log/manticore/query.log
/* Mon Jun 24 05:49:43.488 2024 conn 980 (127.0.0.1:35328) real 0.000 */ select 1;
/* Mon Jun 24 05:49:43.489 2024 conn 980 (127.0.0.1:35328) real 0.000 */ /* DONE */;
/* Mon Jun 24 05:50:30.854 2024 conn 1452 (127.0.0.1:42638) real 0.000 */ select 1;
/* Mon Jun 24 05:50:30.854 2024 conn 1452 (127.0.0.1:42638) real 0.000 */ /* DONE */;

[starinacool]

Hi.

The problem accrues only when connection is being reused by PHP under PHP-fpm.

When PHP takes connection from connection pool, it executes change_user command to clear all possible unfinished transactions and clear session variables.

Does manticore support change_user mysql command?

[sanikolaev]

I see. In this case, the error makes sense. change_user is not supported on Manticore's side since there's no users yet. Let's consider this issue a feature request then. I'll update the body accordingly.

[sanikolaev]

@starinacool you said

Previous version of Manticore was fine.

can you retest it since it's highly unlikely?

[starinacool]

It's a bit challenging to retest because we've upgraded all hosts to the new version. I know this wasn't an issue before since there were no such messages in the query.log. However, since it's just a message in the query.log, it's not a big deal.

I think it would be helpful if Manticore simply responded with "OK" to the change_user command as a quick fix.

The change_user command is quite useful as it resets session variables. It could be beneficial for Manticore as well.

Best Regards.

[sanikolaev]

I confirm:

/* Mon Jun 24 09:26:08.325 2024 conn 1 (172.17.0.1:48708) */  # error=unknown command (code=17)

wasn't logged in 6.2.12, but otherwise the symptoms were the same:

snikolaev@dev2:~$ docker run --name manticore --rm -d -p 29306:9306 manticoresearch/manticore:6.2.12
ad7e7d59b3045b5ccba8af92da9d1b147fc4c4882ba9becdaf4bfb6977f09e33
snikolaev@dev2:~$

snikolaev@dev2:~$ cat test_persistent.php
<?php
$conn=mysqli_connect('p:127.0.0.1','','','',29306) or die();
mysqli_change_user($conn, 'user', 'pw', 'db');

snikolaev@dev2:~$ php test_persistent.php
PHP Fatal error:  Uncaught mysqli_sql_exception: unknown command (code=17) in /home/snikolaev/test_persistent.php:3
Stack trace:
#0 /home/snikolaev/test_persistent.php(3): mysqli_change_user()
#1 {main}
  thrown in /home/snikolaev/test_persistent.php on line 3