0

I am totally confused regarding versions and I am not an expert for PHP/MySQL combinations.

Some Background

Having two systems: 1. my local dev machine (Ubuntu 20), 2. the production server (CentOS 7).

It seems like the production server uses (info page):

  1. PHP Version 7.2.34
  2. Build Provider Remi's RPM repository https://rpms.remirepo.net/
  3. Server API FPM/FastCGI
  4. Configuration File (php.ini) Path /etc/opt/remi/php72
  5. Client API library version mysqlnd 5.0.12-dev - 20150407 - ...
  6. iconv library version 2.17
  7. MySQL: mysql Ver 14.14 Distrib 5.6.47, for Linux (x86_64)
  8. but from sudo yum list installed | grep mysql: php72-php-mysqlnd.x86_64 7.2.34-10.el7.remi @remi-safe

The same for my machine:

  1. PHP Version 7.4.3
  2. Server API FPM/FastCGI
  3. Loaded Configuration File /etc/php/7.4/fpm/php.ini
  4. Client API library version mysqlnd 7.4.3
  5. iconv library version 2.31
  6. MySQL: mysql Ver 8.0.29-0ubuntu0.20.04.3 for Linux on x86_64
  7. From sudo apt list --installed | grep mysql just php-mysql/focal,focal,now 2:7.4+75 all and php7.4-mysql/focal-updates,focal-security,now 7.4.3-4ubuntu2.10 amd64

Problem

In our PHP code we are using a query like $sql1 = "SELECT smth FROM somewhere WHERE smth.id =".(int)parameterId, then $sql1 =iconv("UTF-8", "WINDOWS-1252", $sql1); and then $res1=mysqli_query($GLOBALS["mysqli_connection"], $sql1) // some trigger_error ....

On my machine (mysqlnd 7.4.3) I am getting an error like ec.api.inc.php Line: 208 error: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '=' in and can avoid this by omitting the iconv step. But on the production server (mysqlnd 5.0.12-dev) it is the other way round.

I think the error refers to a mixed or wrong character set in the query, so it is not related to the character set of the database, table or column. (I have used the very same dump.)

Charsets in DB

Finally two examples of char sets for making sure what we are talking about, these tables/views are used in the actual queries:

My local machine
  1. SHOW CREATE TABLE zaehler;: ENGINE=MyISAM AUTO_INCREMENT=4061 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
  2. SHOW CREATE VIEW staende_view;: CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW staende_view [...] from (staende join zaehler on((zaehler.ID = staende.zID))) | utf8mb4 | utf8mb4_unicode_ci |
Production server
  1. SHOW CREATE TABLE zaehler: ENGINE=MyISAM AUTO_INCREMENT=4061 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
  2. SHOW CREATE VIEW staende_view: CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW staende_view [...] from (staende join zaehler on((zaehler.ID = staende.zID))) | utf8mb4 | utf8mb4_unicode_ci |

We have a mix of char sets in the tables. I know this is odd, weird and broken (we are dealing with a database here, which is roughly 15 years old). But I am interested in the behavior of iconv, because this leads to errors in different versions of mysqlnd.

Solution?

Now, how can I make sure, that PHP uses some version of mysqlnd at all?

  1. We have php72-php-mysqlnd.x86_64 7.2.34-10.el7.remi on the production server, but apparently it is not used, how can I change this?
  2. I do not have a mysqlnd package on my machine, how can I install and use a certain version in order to create a more similar environment?
  3. The queries do look equal, when I output them (firstly by using iconv, then without), how can I debug the error message any better way?
BairDev
  • 2,865
  • 4
  • 27
  • 50
  • 1
    This is like, what color I need to paint my AR-16 (for usage of can opener) so I can boil some eggs? – Your Common Sense May 24 '22 at 13:13
  • It looks like you are using wrong approach for this problem. Why are you using ICONV with database operations at all? Why not set the right connection charset and forget about any charset conversations in PHP code? – Dharman May 24 '22 at 13:15
  • Also, to find out your mysqlnd version just look at the PHP version. They are the same because mysqlnd is the bundled native PHP extension. – Dharman May 24 '22 at 13:16
  • "*I do not have a mysqlnd package on my machine, how can I install and use a certain version in order to create a more similar environment?*" You can't. It's bundled with PHP if PHP was compiled with that library – Dharman May 24 '22 at 13:16
  • Also, I can't understand what does mysqlnd version have to do with ICONV? They are not connected in any way. On top of that, your issue seems to have nothing to do with either of these extensions. – Dharman May 24 '22 at 13:18
  • Not only mysqlnd has *absolutely* nothing to do with the problem, but there must be no iconv() either. A database already can convert any encoding on demand. But the thing is, you don't need no conversions either. Your best bet is to have utf-8 everywhere. – Your Common Sense May 24 '22 at 13:19
  • @Dharman thanks for pushing me back to reality and sorry that I am still learning. Adjusting the server charset worked as a first step, now I am trying to get rid of `iconv`. – BairDev May 25 '22 at 10:06

0 Answers0