|
Can you give an example query?
|
|
Yes of course. Here is a simple PHP file executing the same query against the maxscale node and the master SQL node behind maxscale. The school_id field is just one example of a string being returned in the maxscale query but an integer being returned in the direct query to the SQL server.
[root@phpapi1 tmp]# cat max_test.php
|
<?php
|
$USER = 'username';
|
$PASSWORD = 'password';
|
$options = [
|
PDO::ATTR_CASE => PDO::CASE_NATURAL,
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
|
PDO::ATTR_STRINGIFY_FETCHES => false,
|
PDO::ATTR_EMULATE_PREPARES => false,
|
];
|
$dbmax = new PDO('mysql:host=172.16.203.10;dbname=database', $USER, $PASSWORD, $options);
|
$dbmaster = new PDO('mysql:host=172.16.203.4;dbname=database', $USER, $PASSWORD, $options);
|
$q = 'SELECT id, school_id from table where id = 202737';
|
$max = $dbmax->query($q, PDO::FETCH_OBJ)->fetch();
|
var_dump('Maxscale', $max);
|
$master = $dbmaster->query($q, PDO::FETCH_OBJ)->fetch();
|
var_dump('master node', $master);
|
|
[root@phpapi1 tmp]# php max_test.php
|
string(8) "Maxscale"
|
object(stdClass)#4 (2) {
|
["id"]=>
|
string(6) "202737"
|
["school_id"]=>
|
string(1) "1"
|
}
|
string(11) "master node"
|
object(stdClass)#5 (2) {
|
["id"]=>
|
int(202737)
|
["school_id"]=>
|
int(1)
|
}
|
|
|
Has anyone been able to reproduce this or have any idea what might cause it?
I'm keen to roll this solution out to production but the minute it's impossible with integers being converted to strings.
Thanks
|
|
Have you tested the latest 2.4 release?
Please add the CREATE TABLE for the table in question.
It would also be good to test whether removing the following affects the result:
PDO::ATTR_EMULATE_PREPARES => false,
|
|
|
Hi and thanks for looking into this.
I have not tested the latest release. I can redeploy the maxscale instance on the latest version and test again if needed. Is it expected that this might be resolved in a later version?
The create table is:
CREATE TABLE `guardian` (
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
`pupil_id` INT(11) NOT NULL,
|
`pupil_id_1` INT(11) NOT NULL,
|
`pupil_id_2` INT(11) NOT NULL,
|
`pupil_id_3` INT(11) NOT NULL,
|
`pupil_id_4` INT(11) NOT NULL,
|
`school_id` INT(11) NOT NULL,
|
`title` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
|
`firstname` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
|
`surname` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
|
`fullname` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
|
`email` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
|
`email_2` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
|
`telephone` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
|
`telephone_2` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
|
`username` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
|
`password` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
|
`password_bcrypt` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
|
`is_password_temp` TINYINT(4) NOT NULL DEFAULT '0',
|
`active` TINYINT(4) NOT NULL,
|
`deleted` TINYINT(4) NOT NULL,
|
`inserted` DATETIME NOT NULL,
|
`updated` DATETIME NOT NULL,
|
`login` DATETIME NOT NULL,
|
`last_login` DATETIME NOT NULL,
|
`email_update_app` TINYINT(4) NOT NULL,
|
`email_update_app_date` DATETIME NOT NULL,
|
`email_update_web` TINYINT(4) NOT NULL,
|
`email_update_web_date` DATETIME NOT NULL,
|
`is_wonde` TINYINT(4) NOT NULL,
|
`wonde_id` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
|
`wonde_mis_id` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
|
`wonde_upi` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
|
`shop_account_amount` FLOAT(12) NOT NULL,
|
`is_test` TINYINT(4) NOT NULL,
|
PRIMARY KEY (`id`) USING BTREE,
|
INDEX `pupil_id` (`pupil_id`, `school_id`) USING BTREE,
|
INDEX `deleted` (`deleted`) USING BTREE,
|
INDEX `fullname` (`fullname`) USING BTREE,
|
INDEX `is_wonde` (`is_wonde`) USING BTREE,
|
INDEX `wonde_id` (`wonde_id`) USING BTREE,
|
INDEX `wonde_mis_id` (`wonde_mis_id`) USING BTREE,
|
INDEX `wonde_upi` (`wonde_upi`) USING BTREE,
|
INDEX `active` (`active`) USING BTREE,
|
INDEX `pupil_id_2` (`pupil_id`) USING BTREE,
|
INDEX `pupil_id_1_2` (`pupil_id_1`) USING BTREE,
|
INDEX `pupil_id_2_2` (`pupil_id_2`) USING BTREE,
|
INDEX `pupil_id_3` (`pupil_id_3`) USING BTREE,
|
INDEX `pupil_id_4` (`pupil_id_4`) USING BTREE,
|
INDEX `pupil_id_1` (`pupil_id_1`, `pupil_id_2`, `pupil_id_3`, `pupil_id_4`, `pupil_id`) USING BTREE,
|
INDEX `is_test` (`is_test`) USING BTREE,
|
INDEX `school_id` (`school_id`) USING BTREE,
|
INDEX `username` (`username`) USING BTREE
|
)
|
COLLATE='utf8_general_ci'
|
ENGINE=InnoDB
|
AUTO_INCREMENT=299617
|
;
|
Removing the PDO::ATTR_EMULATE_PREPARES => false, results in both instances returning a string instead of integer.
|
|
Please test with the latest release, this helps identify whether it is a new problem or simply a side-effect of an already fixed bug.
|
|
With the latest 2.4 release and the following SQL used to initialize the table, I was not able to reproduce it.
CREATE TABLE `guardian` (
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
`school_id` INT(11) NOT NULL
|
)
|
COLLATE='utf8_general_ci'
|
ENGINE=InnoDB
|
AUTO_INCREMENT=299617;
|
|
INSERT INTO guardian(school_id) VALUES (1);
|
This is what was returned:
string(8) "Maxscale"
|
object(stdClass)#4 (2) {
|
["id"]=>
|
int(299617)
|
["school_id"]=>
|
int(1)
|
}
|
string(11) "master node"
|
object(stdClass)#5 (2) {
|
["id"]=>
|
int(299617)
|
["school_id"]=>
|
int(1)
|
}
|
|
|
Sorry for the delay.
Having upgraded to Maxscale 2.4.10 from 2.4.4 the same issue is present.
[root@schspid-max-upgrade ~]# maxscale -v
|
MaxScale 2.4.10
|
=============
|
# php max_test.php
|
string(8) "Maxscale"
|
object(stdClass)#4 (2) {
|
["id"]=>
|
string(6) "202737"
|
["school_id"]=>
|
string(1) "1"
|
}
|
string(11) "master node"
|
object(stdClass)#5 (2) {
|
["id"]=>
|
int(202737)
|
["school_id"]=>
|
int(1)
|
}
|
|
|
Can you add a minimal CREATE TABLE and example data that can be used to reproduce the issue? With the information I provided above it doesn't seem to reproduce.
|
|
Hi
With this minimal create code:
<code>
CREATE TABLE `guardian` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`school_id` INT(11) NOT NULL,
PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=299618
;
INSERT INTO repltest.guardian(school_id) VALUES (1);
</code>
My query still returns strings via maxscale but integers via master and slave direct queries:
<code>
php max_repltest.php
string(8) "Maxscale"
object(stdClass)#5 (2)
{
["id"]=>
string(6) "299617"
["school_id"]=>
string(1) "1"
}
string(11) "master node"
object(stdClass)#6 (2)
{
["id"]=>
int(299617)
["school_id"]=>
int(1)
}
string(10) "slave node"
object(stdClass)#7 (2) {
["id"]=>
int(299617)
["school_id"]=>
int(1)
}
</code>
|
|
DBA666 what version of PHP are you using?
|
|
Hey Markus.
The php version in use is 7.4.6.
Cheers
|
|
We haven't been able to reproduce this at all. Have you tested with the latest 2.5 release of MaxScale to see whether it happens with that version as well?
|
|
Closing as Cannot Reproduce due to us never being able to reproduce it and there being no reproducible test case.
|
|
I faced the same issue.
I just installed debian and installed the same PHP version as in our production environment.
apt -y install lsb-release apt-transport-https ca-certificates
|
wget -O /etc/apt/trusted.gpg.d/php.gpg https://packages.sury.org/php/apt.gpg
|
echo "deb https://packages.sury.org/php/ $(lsb_release -sc) main" | sudo tee /etc/apt/sources.list.d/php.list
|
apt update
|
apt install php8.0-cli php8.0-mysql
|
With PHP version 8.1 (8.1.10-1+0~20220914.25+debian11~1.gbp7fe5a8, apt install php8.1-cli php8.1-mysql), there are no such problems.
From https://www.php.net/manual/en/migration81.incompatible.php
"Integers and floats in result sets will now be returned using native PHP types instead of strings when using emulated prepared statements. This matches the behavior of native prepared statements. The previous behaviour can be restored by enabling the PDO::ATTR_STRINGIFY_FETCHES option."
$ php8.0 maxscale-pdo.php
|
|
string(13) "From maxscale"
|
object(stdClass)#4 (1) {
|
["id"]=>
|
string(4) "6997"
|
}
|
string(16) "From master node"
|
object(stdClass)#5 (1) {
|
["id"]=>
|
int(6997)
|
}
|
|
maxscale-pdo.php
<?php
|
|
$USER = 'user';
|
$PASSWORD = 'password';
|
$DB_NAME = 'db';
|
$TABLE_NAME = 'table';
|
$MAXSCALE_HOST = 'maxscale.domain.example.com';
|
$MYSQL_HOST = 'master.domain.example.com';
|
|
$options = [
|
// PDO::ATTR_STRINGIFY_FETCHES => false,
|
PDO::ATTR_EMULATE_PREPARES => false,
|
];
|
|
$dbmax = new PDO("mysql:host=$MAXSCALE_HOST;dbname=$DB_NAME", $USER, $PASSWORD, $options);
|
$dbmaster = new PDO("mysql:host=$MYSQL_HOST;dbname=$DB_NAME", $USER, $PASSWORD, $options);
|
|
$q = "SELECT id FROM $TABLE_NAME WHERE id=6997";
|
|
$max = $dbmax->query($q, PDO::FETCH_OBJ)->fetch();
|
var_dump('From maxscale', $max);
|
|
$master = $dbmaster->query($q, PDO::FETCH_OBJ)->fetch();
|
var_dump('From master node', $master);
|
"PDO::ATTR_EMULATE_PREPARES => false": This option somehow affects maxscale.
Maxscale: 6.4.2~bullseye-1
Debian GNU/Linux 11 (bullseye)
percona-xtradb-cluster-57 5.7.38-31.59-1.stretch
PHP:
php-common 2:92+0~20220117.43+debian11~1.gbpe0d14e
php8.0-cli 1:8.0.22-1+0~20220815.40+debian11~1.gbpf6bdf4
php8.0-common 1:8.0.22-1+0~20220815.40+debian11~1.gbpf6bdf4
php8.0-mysql 1:8.0.22-1+0~20220815.40+debian11~1.gbpf6bdf4
php8.0-opcache 1:8.0.22-1+0~20220815.40+debian11~1.gbpf6bdf4
php8.0-readline 1:8.0.22-1+0~20220815.40+debian11~1.gbpf6bdf4
|
|
I still wasn't able to reproduce it. This time I used the percona:5.7 and maxscale:6.4 docker images to make the test easier to reproduce. I ran this script in a debian:11 container:
#!/bin/bash
|
apt -y install lsb-release apt-transport-https ca-certificates wget sudo
|
wget -O /etc/apt/trusted.gpg.d/php.gpg https://packages.sury.org/php/apt.gpg
|
echo "deb https://packages.sury.org/php/ $(lsb_release -sc) main" | sudo tee /etc/apt/sources.list.d/php.list
|
apt update
|
apt install php8.0-cli php8.0-mysql
|
and ran a modified version of the test:
root@monolith:/# php --version
|
PHP 8.0.22 (cli) (built: Aug 15 2022 09:50:12) ( NTS )
|
Copyright (c) The PHP Group
|
Zend Engine v4.0.22, Copyright (c) Zend Technologies
|
with Zend OPcache v8.0.22, Copyright (c), by Zend Technologies
|
root@monolith:/# php test.php
|
PDO::ATTR_STRINGIFY_FETCHES => false, PDO::ATTR_EMULATE_PREPARES => false
|
string(13) "From maxscale"
|
object(stdClass)#4 (1) {
|
["id"]=>
|
int(6997)
|
}
|
string(16) "From master node"
|
object(stdClass)#5 (1) {
|
["id"]=>
|
int(6997)
|
}
|
PDO::ATTR_STRINGIFY_FETCHES => true, PDO::ATTR_EMULATE_PREPARES => false
|
string(13) "From maxscale"
|
object(stdClass)#1 (1) {
|
["id"]=>
|
string(4) "6997"
|
}
|
string(16) "From master node"
|
object(stdClass)#3 (1) {
|
["id"]=>
|
string(4) "6997"
|
}
|
PDO::ATTR_STRINGIFY_FETCHES => false, PDO::ATTR_EMULATE_PREPARES => true
|
string(13) "From maxscale"
|
object(stdClass)#5 (1) {
|
["id"]=>
|
string(4) "6997"
|
}
|
string(16) "From master node"
|
object(stdClass)#2 (1) {
|
["id"]=>
|
string(4) "6997"
|
}
|
PDO::ATTR_STRINGIFY_FETCHES => true, PDO::ATTR_EMULATE_PREPARES => true
|
string(13) "From maxscale"
|
object(stdClass)#3 (1) {
|
["id"]=>
|
string(4) "6997"
|
}
|
string(16) "From master node"
|
object(stdClass)#4 (1) {
|
["id"]=>
|
string(4) "6997"
|
}
|
here's the modified test:
<?php
|
|
function test($options){
|
$USER = 'maxuser';
|
$PASSWORD = 'maxpwd';
|
$DB_NAME = 'test';
|
$TABLE_NAME = 't1';
|
$MAXSCALE_HOST = '127.0.0.1';
|
$MYSQL_HOST = '127.0.0.1';
|
|
|
$dbmaster = new PDO("mysql:host=$MYSQL_HOST;port=3000;dbname=$DB_NAME", $USER, $PASSWORD, $options);
|
$dbmax = new PDO("mysql:host=$MAXSCALE_HOST;port=4006;dbname=$DB_NAME", $USER, $PASSWORD, $options);
|
|
$q = "SELECT id FROM $TABLE_NAME WHERE id=6997";
|
|
$max = $dbmax->query($q, PDO::FETCH_OBJ)->fetch();
|
var_dump('From maxscale', $max);
|
|
$master = $dbmaster->query($q, PDO::FETCH_OBJ)->fetch();
|
var_dump('From master node', $master);
|
}
|
|
|
$options1 = [PDO::ATTR_STRINGIFY_FETCHES => false, PDO::ATTR_EMULATE_PREPARES => false];
|
$options2 = [PDO::ATTR_STRINGIFY_FETCHES => true, PDO::ATTR_EMULATE_PREPARES => false];
|
$options3 = [PDO::ATTR_STRINGIFY_FETCHES => false, PDO::ATTR_EMULATE_PREPARES => true];
|
$options4 = [PDO::ATTR_STRINGIFY_FETCHES => true, PDO::ATTR_EMULATE_PREPARES => true];
|
|
printf("PDO::ATTR_STRINGIFY_FETCHES => false, PDO::ATTR_EMULATE_PREPARES => false\n");
|
test($options1);
|
printf("PDO::ATTR_STRINGIFY_FETCHES => true, PDO::ATTR_EMULATE_PREPARES => false\n");
|
test($options2);
|
printf("PDO::ATTR_STRINGIFY_FETCHES => false, PDO::ATTR_EMULATE_PREPARES => true\n");
|
test($options3);
|
printf("PDO::ATTR_STRINGIFY_FETCHES => true, PDO::ATTR_EMULATE_PREPARES => true\n");
|
test($options4);
|
[markusjm@monolith]$ docker exec -ti server mysql -uroot -e "select @@version, @@version_comment"
|
+---------------+----------------------------------------------------+
|
| @@version | @@version_comment |
|
+---------------+----------------------------------------------------+
|
| 5.7.35-38-log | Percona Server (GPL), Release 38, Revision 3692a61 |
|
+---------------+----------------------------------------------------+
|
[markusjm@monolith]$ docker exec -ti maxscale head -n 15 /var/log/maxscale/maxscale.log|grep Commit
|
2022-09-16 14:28:06 notice : MariaDB MaxScale 6.4.2 started (Commit: de2f986518f946ddb7219d6b5b9c45b37ddd17b0)
|
|
|
Would it be possible for you to get a network capture with Wireshark for both the MaxScale and the Percona test cases? This could help us figure out what's happening.
|
|
"PDO::ATTR_STRINGIFY_FETCHES => false" + "PDO::ATTR_EMULATE_PREPARES => false"
maxscale642.php80.pdo.pcap maxscale2417.php80.pdo.pcap percona57.php80.pdo.pcap
|
|
I did some more tests. The "readwritesplit" router returns a string. The "readconnroute" router returns a number.
I made 4 traffic dumps, two between client and maxscale and two between maxscale and server
maxscale642-readwrite-mysql57.pcap
maxscale642-readconnroute-mysql57.pcap
php80-pdo-client-maxscale642-readconnroute.pcap
php80-pdo-client-maxscale642-readwrite.pcap
|
|
Logs from maxscale:
maxscale642-readconnroute.logs
maxscale642-readwritesplit.logs
|
|
Were you able to reproduce the issue? How else can I help?
|
|
I must've missed those pcap file uploads, I only now saw them. I'll analyze them and see if I find anything interesting.
|
|
The only difference that I found between php80-pdo-client-maxscale642-readconnroute.pcap and php80-pdo-client-maxscale642-readwrite.pcap is that the readwritesplit instance seems to be configured with a custom version string. Once I added an invalid version string (e.g. version_string=some-random-garbage) I was able to reproduce the problem and it made PHP think it's talking to some old MySQL version. This caused it to switch to the text protocol instead of the binary protocol which caused the result to be converted into a string.
Can you try if adding something like 5.7.30 at the start of any of your custom version strings solves the problem for you?
|
|
It was in this setting.
Since the version number did not start with "5" or "8", "5.5.5" was added before the version number.
Thank you. Now everything works as it should.
|
maxscale2417.php80.pdo.pcap