MySQLi constant, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, is undefined

[cbj4074]

Hello,

I recently updated my DB abstraction layer to use the MySQLi driver, instead of the obsoleted MySQL driver.

On one server, the transition was perfectly smooth. On another server, however, I receive the following output:

Notice: Use of undefined constant MYSQLI_OPT_INT_AND_FLOAT_NATIVE - assumed 'MYSQLI_OPT_INT_AND_FLOAT_NATIVE' in ...

The scarcity of information regarding MYSQLI_OPT_INT_AND_FLOAT_NATIVE constant is surprising. I can't find any instance of the above string on the Web.

The MySQLi extension is definitely enabled on the server in question (I confirmed this via phpinfo() output). Here are the server's vitals:

PHP: 5.3.2-1ubuntu4.17
MySQLi Client API library version: 5.1.63

Am I missing something obvious?

Thanks for any help!

[bradgrafelman]

Can you show us what the PHP statements are around the line number identified in the error message?

[cbj4074]

Hi, Brad, and thanks for the reply.

Sure:

$mysqli = new mysqli($dbServer, $dbUser, $dbPass);

if (!is_null($mysqli->connect_error)) {
	trigger_error('Could not connect to the "' . $dbServer . '" database server; double-check the username and password supplied. Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error, E_USER_WARNING);
	return FALSE;
}

//Instruct PHP to preserve numeric MySQL data types (otherwise, all results
//are returned as strings).
$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);

This seems to be related to the library that the driver/extension uses, which appears to be mysqlnd on the server on which this notice does not occur. Conversely, mysqlnd is not enabled on the server on which this notice is raised (there's no mention of it in the phpinfo() outout, but then again, there's no mention of libmysql there, either). The PHP manual does state that the MYSQLI_OPT_INT_AND_FLOAT_NATIVE constant applies only to mysqld. From the manual:

http://php.net/manual/en/mysqli.quickstart.statements.php

It is possible to convert integer and float columns back to PHP numbers by setting the MYSQLI_OPT_INT_AND_FLOAT_NATIVE connection option, if using the mysqlnd library. If set, the mysqlnd library will check the result set meta data column types and convert numeric SQL columns to PHP numbers, if the PHP data type value range allows for it. This way, for example, SQL INT columns are returned as integers.

http://dev.mysql.com/downloads/connector/php-mysqlnd/

The extensions can either use the mysqlnd or libmysql library to connect from PHP to MySQL.

So, it seems that the server on which this problem occurs is using libmysql and not mysqlnd.

If this is the case, is it simple enough to switch from libmysql to mysqlnd? Or is there an equivalent constant for libmysql, if switching the library is not possible or desirable?

UPDATE: This thread seems to describe my problem: http://stackoverflow.com/questions/3407823/mysqlnd-driver-in-php-5-3-2-on-ubuntu-10-04 . I suppose I have two choices: don't use the constant, or build PHP from source -- with mysqlnd support -- on the server in question.

[bradgrafelman]

cbj4074;11014907 wrote:

If this is the case, is it simple enough to switch from libmysql to mysqlnd?

Depends what is "simple" to you. 🙂 Looks like you're using an Ubuntu package manager (Aptitude, I'm guessing?) for PHP, so what you'd need to do is uninstall the package that provides the mysql/MySQLi extensions that use the libmysqlclientXX (where XX is an integer, probably 16) library and instead install a package that utilizes PHP's mysqlnd library.

However, I'm guessing you're running Ubuntu Lucid (ver 10.04), and I don't see any mysqlnd-based packages available in the Lucid channel. This means you'd probably have to compile PHP yourself from scratch.

cbj4074;11014907 wrote:

Or is there an equivalent constant for libmysql, if switching the library is not possible or desirable?

The PHP source code (here) suggests that this option is only available if PHP was compiled using the mysqlnd library. There doesn't seem to be any equivalent for libmysql.

So your options appear to be: 1) upgrade Ubuntu, 2) compile PHP yourself, 3) don't use the above option (do you really need it in the first place?).

EDIT: Didn't see your "UPDATE:" before replying. I think you've accurately assessed the situation and arrived at the same result as I did.

EDIT2: Then again, the StackOverflow question you linked to seemed to suggest that you could just install the php5-mysqlnd package from the 'Precise' channel even though you're on 10.04 (Lucid). I'm no Ubuntu expert, so I can't say whether or not that would be issue-free; I'd definitely suggest not doing this for the first time on a live production server.

[cbj4074]

Thanks for such a thorough response, Brad! Your time is very much appreciated.

You've summed-up the situation quite nicely. And you're right about everything:

• Yes, I am using Ubuntu 10.04.
• Yes, I am using Aptitude.
• Yes, there are no packages for mysqlnd available from the Ubuntu 10.04 repositories.

I tried installing the "php5-mysqlnd" package from the "Precise" channel, as recommended in the StackOverflow thread, but the following results:

apt-get --dry-run install php5-mysqlnd
Reading package lists... Done
Building dependency tree
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
  php5-mysqlnd: Depends: phpapi-20090626
E: Broken packages

Upgrading to Ubuntu 12.04 is on the list of to-dos, but it won't be anytime in the immediate future. So, that option is out.

Compiling PHP myself seems too risky; this is a large-scale production system and there's too much that I would need to test after building from source to make this option practical for me.

It seems we're onto option 3: don't use the constant. And no, I don't "need" it, per se, but it's a nice-to-have. We use Sencha's ExtJS framework and it requires proper data typing. While it is possible to type-cast in ExtJS, I found the native PHP solution (using this constant) to be a more elegant solution.

Oh well. I guess we'll have to wait until we upgrade to PHP 5.4, which will probably happen when we move to Ubuntu 12.04.

Thanks for all your help here, Brad.

[bradgrafelman]

The only other option would be upgrading PHP completely based on the packages in the 12.04 channel. The only issue there is that there may be other library dependencies that are also only available in the 12.04 channel. Apt should handle that on its own, but you'd just want to make sure it doesn't build this huge list of external dependencies and leaves you to play the equivalent of DLL hell for any other applications on your server.