MYSQL Doesn't return native datatypes

[PatRoy]

Hello,

My current server setup :

PHP version: 7.1.4
MySQL 5.5.28 
Database client version: libmysql - mysqlnd 5.0.12-dev

I'm using PDO _MYSQL to run database queries. 

From what I read, in order to have MySQL return native datatypes (as opposed to just strings) : 
 

1. if using MySQLi: set $mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1)
2. if using PDO and have mysqlnd:  "mysqlnd returns native data types when using Server-side Prepared Statements, for example an INT column is returned as an integer variable not as a string."

Now, I do have mysqlnd, but a simple PDO Prepared statement still returns me all strings, even though some columns are set as INT, VARCHAR, DATETIME, etc..

$stmt = $pdo->prepare("SELECT * FROM tbl_users WHERE id=1");
$stmt->execute();
$user = $stmt->fetch();

var_dump($user);

OUTPUT:
array(13) {
  ["id"]=>
  string(1) "1"
  ["is_admin"]=>
  string(1) "1"
  ["is_active"]=>
  string(1) "1"
  ["username"]=>
  string(5) "jdoe"
...
}

 

I'm not sure what to look for to resolve this issue if I want it to return INT as INT and not STRING, etc. 

Does having `extension_loaded('mysqlnd') == true`,  make my system automatically use this native mysql driver? How can I tell ? Could this be a version problem perhaps?

 

Much thanks!

Pat

 

[mac_gyver]

38 minutes ago, PatRoy said:

Server-side Prepared Statements

the key to this working is probably emulated prepared queries vs true prepared queries. when you make the database connection are you setting the pdo emulated prepared query attribute to a false value?

are you really sure you want or care to do this? since this doesn't return the data types for a non-prepared query you will always need to use a prepared query, with the overhead of an extra round-trip communication with the database server, for every  SELECT query, even if you are not supplying any data via a place-holder.

 

[benanamen]

What is the real problem you are trying to solve by doing this?

[Barand]

For the record, with PDO I get the types with query() and prepare() (although, as expected, dates are "string")

$res = $db->query("select id 
                        , date
                        , opposition
                        , attendance
                   from seasonstats 
                   limit 1");
$r = $res->fetch();
echo '<pre> query result <br>';
var_dump($r);

$res = $db->prepare("select id 
                        , date
                        , opposition
                        , attendance
                   from seasonstats 
                   limit 1");
$res->execute();
$r = $res->fetch();
echo '<pre> prepare result <br>';
var_dump($r);

Results

 query result 
array(4) {
  ["id"]=>
  int(1)
  ["date"]=>
  string(10) "2010-08-07"
  ["opposition"]=>
  string(14) "Crystal Palace"
  ["attendance"]=>
  int(17486)
}
 prepare result 
array(4) {
  ["id"]=>
  int(1)
  ["date"]=>
  string(10) "2010-08-07"
  ["opposition"]=>
  string(14) "Crystal Palace"
  ["attendance"]=>
  int(17486)
}

 

[PatRoy]

On 6/6/2020 at 2:49 PM, mac_gyver said:

the key to this working is probably emulated prepared queries vs true prepared queries. when you make the database connection are you setting the pdo emulated prepared query attribute to a false value?

are you really sure you want or care to do this? since this doesn't return the data types for a non-prepared query you will always need to use a prepared query, with the overhead of an extra round-trip communication with the database server, for every  SELECT query, even if you are not supplying any data via a place-holder.

 

Thanks for your reply!

Well, indeed, I do set my DB connection with PDO::ATTR_EMULATE_PREPARES => FALSE. Would that mean that MySQL automatically just return strings, but if allowing PHP/PDO to do the prepares, then IT only can properly prepare and return native datatypes?

I had set it to false, in part to protect against SQL Injections, though I don't remember how this worked out for this... 

To answer others, I needed to resolve this because I am writing my own ORM system, which loads objects from database into an array of properties:

array(11) {
   ["id"]=>
  array(8) {
    ["column"]=>
    string(2) "id"
    ["type"]=>
    string(7) "integer"
    ["readonly"]=>
    bool(true)
    ["required"]=>
    bool(false)
    ["value"]=>
    string(1) "1"
    ["valueIfNull"]=>
    NULL
    ["useDBDefaultWhenNull"]=>
    bool(false)
    ["datetimeformat"]=>
    NULL
  }
  ["dateModified"]=>
  array(8) {
    ["column"]=>
    string(13) "date_modified"
    ["type"]=>
    string(8) "datetime"    
    ["readonly"]=>
    bool(false)
    ["required"]=>
    bool(false)
    ["value"]=>
    string(19) "2020-06-07 22:35:24"
    ["valueIfNull"]=>
    NULL
    ["useDBDefaultWhenNull"]=>
    bool(false)
    ["datetimeformat"]=>
    NULL
  }
  ....
}

 

As you can see, the "value" are always strings. I have a validDatatype() check that validates $value before submitting into the database. To do so, I simply run a check that gettype($value) is the same as my prop "type".

Initially, I wanted to do all of the datatype validation within my save() method. However, if when loading all fields are strings, when I go ahead, mod some values, and then save(), it'll fail datatype validation!

My current workaround is to do my validation on my property setters only, instead of within the save(), but I still wondered why I was getting all strings from MySQL....

Cheers for your replies! P.

 

[kicken]

What exactly does it say under the pdo_mysql section of your phpinfo() output?

[PatRoy]

7 minutes ago, kicken said:

What exactly does it say under the pdo_mysql section of your phpinfo() output?

PDO

PDO support => enabled
PDO drivers => mysql

pdo_mysql

PDO Driver for MySQL => enabled
Client API version => 5.5.28
 

[kicken]

I don't think you are using the mysqlnd driver, your using the libmysql instead.  If it were the ND driver I'd expect it to say something more like:

pdo_mysql
PDO Driver for MySQL    enabled
Client API version     mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $

You'll need to look into changing your driver or just work around the string values.

[PatRoy]

11 minutes ago, kicken said:

I don't think you are using the mysqlnd driver, your using the libmysql instead.  If it were the ND driver I'd expect it to say something more like:

pdo_mysql
PDO Driver for MySQL    enabled
Client API version     mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $

You'll need to look into changing your driver or just work around the string values.

I'm not sure I understand how to look / confirm I'm using the mysqlnd driver... Even less how to force use it.. 

I do have extension=pdo_mysql.so loaded in my php.ini, which I thought was loading the mysqlnd driver (guess not!?).

And, when I load the web phpMyAdmin to managemy DBs, the homepage does give me some info on the server, one of them being: Database client version: libmysql - mysqlnd 5.0.12-dev - 2015040. See screenshot attachment... 

Is the mysqlnd loaded 'per web application' ?

 

[PatRoy]

13 minutes ago, PatRoy said:

I'm not sure I understand how to look / confirm I'm using the mysqlnd driver... Even less how to force use it.. 

I do have extension=pdo_mysql.so loaded in my php.ini, which I thought was loading the mysqlnd driver (guess not!?).

And, when I load the web phpMyAdmin to managemy DBs, the homepage does give me some info on the server, one of them being: Database client version: libmysql - mysqlnd 5.0.12-dev - 2015040. See screenshot attachment... 

Is the mysqlnd loaded 'per web application' ?

 

UPDATE: Upon reading https://www.php.net/manual/en/mysqlnd.install.php, it seems the mysqlnd is enabled by a ./configure option on either mysqli, or pdo_mysql...  Looking at the ./configure command options within phpinfo(), I see the following two:

--with-mysqli=shared,mysqlnd
--with-pdo-mysql=shared,/ffp

Would that mean that mysqli has mysqlnd enabled (thus the reason I see it in phpMyAdmin since it uses mysqli), and my pdo_mysql  doesn't have it enabled (assuming, since the lack of it in the configure above) ?

HOWEVER, if I just do:
var_dump(extension_loaded('mysqlnd'));

it returns TRUE. So, super confused here... but then again, is it telling me true, just because it's 'loaded' only if using mysqli to query the database?

Edited June 8, 2020 by PatRoy

[kicken]

4 hours ago, PatRoy said:

Would that mean that mysqli has mysqlnd enabled (thus the reason I see it in phpMyAdmin since it uses mysqli), and my pdo_mysql  doesn't have it enabled (assuming, since the lack of it in the configure above) ?

It sounds to me like that might be the case, which is quite an unusual setup.  Generally speaking your only supposed to use one or the other, not have both.  I don't think there is any way to change which is used other than re-compiling PHP with the correct flags.  If you didn't build PHP yourself and instead got it from your OSs package manager you may have the wrong packages installed.

 

var_dump($db->getAttribute(PDO::ATTR_DRIVER_NAME));
var_dump($db->getAttribute(PDO::ATTR_CLIENT_VERSION));

Will tell you what driver/version your using.

[PatRoy]

15 hours ago, kicken said:

It sounds to me like that might be the case, which is quite an unusual setup.  Generally speaking your only supposed to use one or the other, not have both.  I don't think there is any way to change which is used other than re-compiling PHP with the correct flags.  If you didn't build PHP yourself and instead got it from your OSs package manager you may have the wrong packages installed.

 

var_dump($db->getAttribute(PDO::ATTR_DRIVER_NAME));
var_dump($db->getAttribute(PDO::ATTR_CLIENT_VERSION));

Will tell you what driver/version your using.

 

Outputs: 

string(5) "mysql"
string(6) "5.5.28"

 

My weird setup is due to the fact that my 'server' is a farely old NAS running a armv5tel  architecture Linux. I had 'kinda' jailed broken the NAS as to get custom servers on it (lighttpd, mysql, etc). But the fact that it's jailbroken, and old, limits me on packages and versions I can install on it (thus the reason its running PHP 7.1). It's pretty much a nightmare to try and compile stuff on it because of missing / eventually broken libraries... 

I think I have no choice but to live with it and accept the returned strings from MySQL by adopting my workaround to do my validation on my property setters only, instead of within my save().

Thanks for your help and times