Welcome! Log In Create A New Profile

Advanced

[PHP-DEV] Mysql result data types

Posted by Reinis Rozitis 
Reinis Rozitis
[PHP-DEV] Mysql result data types
October 12, 2017 02:00PM
Hello,
is there a reason (technical or historical) why the data coming from MySQL is always strings?
I've found only one case where the data type is "honored" - PDO+mysqlnd+emulation off [1]

We made a fairly simple patch to 'mysqlnd' which enables (configurable via ini) data to be returned (trying to match) as defined in database/table.

In general something like:

switch( field->type ){
case MYSQL_TYPE_TINY:
case MYSQL_TYPE_SHORT:
case MYSQL_TYPE_LONG:
case MYSQL_TYPE_LONGLONG:
case MYSQL_TYPE_INT24:
convert_to_long(data);
break;
case MYSQL_TYPE_DECIMAL:
case MYSQL_TYPE_DOUBLE:
case MYSQL_TYPE_FLOAT:
case MYSQL_TYPE_NEWDECIMAL:
convert_to_double(data);
break;
}

Does it make sense to create a PR and/or RFC for something like this?


[1] https://phpdelusions.net/pdo#returntypes


wbr
Reinis Rozitis


--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Am 12.10.2017 um 13:55 schrieb Reinis Rozitis:
> is there a reason (technical or historical) why the data coming from MySQL is always strings?
> I've found only one case where the data type is "honored" - PDO+mysqlnd+emulation off [1]

not entirely true, our database-layer acts directly with mysqli and
unconditionally enables native types for the last 8 months or so after
we mirgated our whole code to PHP7 native types

no idea what the state of PDO is


http://blog.ulf-wendel.de/2008/php-new-network-traffic-cpu-and-memory-savings-with-mysqlnd/

if(mysqli_options($this->conn, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true)
=== false)
{
$this->error('mysqli_options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE) failed');
}

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Reinis Rozitis
RE: [PHP-DEV] Mysql result data types
October 12, 2017 02:40PM
> no idea what the state of PDO is
>
>
> http://blog.ulf-wendel.de/2008/php-new-network-traffic-cpu-and-memory-
> savings-with-mysqlnd/
>
> if(mysqli_options($this->conn, MYSQLI_OPT_INT_AND_FLOAT_NATIVE,

Thanks,
as we still partly (forced to) live in the "deprecated or moved to pecl" ext/mysql world this gave the idea to actually implement the int_and_float_native into the extension rather than alter the driver (which apparently already has such functionality).

rr


--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Am 12.10.2017 um 14:34 schrieb Reinis Rozitis:
>> no idea what the state of PDO is
>>
>>
>> http://blog.ulf-wendel.de/2008/php-new-network-traffic-cpu-and-memory-
>> savings-with-mysqlnd/
>>
>> if(mysqli_options($this->conn, MYSQLI_OPT_INT_AND_FLOAT_NATIVE,
>
> Thanks,
> as we still partly (forced to) live in the "deprecated or moved to pecl" ext/mysql world this gave the idea to actually implement the int_and_float_native into the extension rather than alter the driver (which apparently already has such functionality)

who forces you to ext/mysql?

don't get me wrong but we implemented 'mysqli' in our database layer a
decade ago, at that time with 'enable_mysqli' which not so much later
defaulted to be enabled that i don't see any code in 2017 which still
did not cope as unmaintained and broken stuff

we stopped build PHP with ext/mysql 4 years ago at all

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Reinis Rozitis
RE: [PHP-DEV] Mysql result data types
October 12, 2017 03:10PM
> who forces you to ext/mysql?

It's out of topic but obviously the code/software and products on the servers.

For me as a system administrator I have choice either to never upgrade (for example https://w3techs.com/technologies/details/pl-php/all/all one can see the rough rate of php version adoption) or work around the issues/lack of features in the deprecated stuff (or answer why I broke everything).

While I prefer bleeding edge it's not always an option (to force everyone) and in general 7.1+ext/mysql+mysqlnd works just fine.

rr


--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Johannes Schlüter
Re: [PHP-DEV] Mysql result data types
October 12, 2017 05:20PM
On Do, 2017-10-12 at 14:55 +0300, Reinis Rozitis wrote:
> Hello,
> is there a reason (technical or historical) why the data coming from
> MySQL is always strings?
> I've found only one case where the data type is "honored" -
> PDO+mysqlnd+emulation off [1]

The reason for this is that this is the way the protocol works. With
non-prepared statements the server sends the data in textual form. With
prepared statements it switches to a different protocol sending values
in binary form.
There was a proposal in the server once that would use binary also for
non-prepared statements, but was rejected, for reasons i don't recall.
(maybe backwards compatibility)
MySQL's new X protocol uses binary representation by defalt, but is no
drop in replacment, but new protocol, with new clients etc.

> We made a fairly simple patch to 'mysqlnd' which enables
> (configurable via ini) data to be returned (trying to match) as
> defined in database/table. 
>
> In general something like:
>
> switch( field->type ){
> case MYSQL_TYPE_TINY:
> case MYSQL_TYPE_SHORT:
> case MYSQL_TYPE_LONG:
> case MYSQL_TYPE_LONGLONG:
> case MYSQL_TYPE_INT24:
> convert_to_long(data);
> break;
> case MYSQL_TYPE_DECIMAL:
> case MYSQL_TYPE_DOUBLE:
> case MYSQL_TYPE_FLOAT:
> case MYSQL_TYPE_NEWDECIMAL:
> convert_to_double(data);
> break;
> }

It's not that trivial: With such a conversion the values might
overflow. What happens if the server returns an huge unsigned value
which PHP's signed integers can't represent? Does the type then change
based  on the specific value?
You're also converting a precise DECIMAL in an imprecise double which
will lose precision.
Also consider that many applications don't do extra calculations on
such returned data, but carry it through and then return as a string
(be it HTML, JSON; XML or whatever) thus have two "useless" conversions
(while carrying a tiny bit more memory around ...)
Traditional in PHP the string is no problem, as the engine will convert
when needed (and only then ...), I don't know how scalar typing in the
large view changed this ..but even in sch a code base here'd be a
conversion only for those who want it ...

> Does it make sense to create a PR and/or RFC for something like this?

If the community wants this, this could be done, I'm a bit skeptical
and I'd suggest to benchmark some systems passing lots of data around.

johannes

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Am 12.10.2017 um 17:16 schrieb Johannes Schlüter:
> On Do, 2017-10-12 at 14:55 +0300, Reinis Rozitis wrote:
>> Hello,
>> is there a reason (technical or historical) why the data coming from
>> MySQL is always strings?
>> I've found only one case where the data type is "honored" -
>> PDO+mysqlnd+emulation off [1]
>
> The reason for this is that this is the way the protocol works. With
> non-prepared statements the server sends the data in textual form. With
> prepared statements it switches to a different protocol sending values
> in binary form.
> There was a proposal in the server once that would use binary also for
> non-prepared statements, but was rejected, for reasons i don't recall.
> (maybe backwards compatibility)
> MySQL's new X protocol uses binary representation by defalt, but is no
> drop in replacment, but new protocol, with new clients etc

as i understand that below MYSQLI_OPT_INT_AND_FLOAT_NATIVE does not
convert but *return* native types and benchamrks support this

http://blog.ulf-wendel.de/2008/php-new-network-traffic-cpu-and-memory-savings-with-mysqlnd/

A new mysqlnd only option gives you 3). Try out mysqli_options($link,
MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true)



--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Sorry, only registered users may post in this forum.

Click here to login