dongyuan1984 2019-04-18 21:46
浏览 75

使用PHP-FPM和MySQL将带有重音字符的文本保存为NULL

I'm building the DevOps infrastructure for a PHP CodeIgniter application. I'm using Kubernetes to setup PHP-FPM, NGINX and MariaDB (MySQL) and all seems to work great except that every time someone tries to save a text field that contains accented characters the text gets saved as NULL instead and no errors are displayed.

Eg.:

  • User name is Testing.
  • Change name to Another Testing. (Goes OK)
  • Change name to Helló Wôrld. (Or anything accented)
  • The name gets saved as "". (Empty string)

I know it's not related with the CodeIgniter application because I know it's working without this issue on other infrastructure (which I don't have access to) with the same code base. So the problem must be with my infrastructure.

The database dump I used to create my copy of the database was exported directly from the working infrastructure, bringing the UTF8 collection with it.

In a attempt to fix this, I have setup my MySQL configuration as the following:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
wait_timeout = 6000
max_allowed_packet = 64M
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

And my database.php CodeIgniter configuration file as:

$db['default'] = array(
    'dsn'   => '',
    'hostname' => $_SERVER["MYSQL_HOST"],
    'username' => $_SERVER["MYSQL_USER"],
    'password' => $_SERVER["MYSQL_PASSWORD"],
    'database' => $_SERVER["MYSQL_DB"],
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => TRUE,
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8mb4',
    'dbcollat' => 'utf8mb4_unicode_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

So I essentially changed it from utf8_general_ci to utf8mb4_unicode_ci.

But it haven't fixed, the issue keeps on happening.

I've checked the HTML metatag for charset as well and it's for UTF-8 too.

Also, probably this is unrelated, but here is my nginx.conf file:

user                            htdocs;
worker_processes                1;

error_log                       /var/log/nginx/error.log warn;
pid                             /var/run/nginx.pid;

events {
    worker_connections          1024;
}

http {
    include                     /etc/nginx/mime.types;
    default_type                application/octet-stream;
    sendfile                    on;
    access_log                  /var/log/nginx/access.log;
    keepalive_timeout           3000;
    set_real_ip_from            127.0.0.1;
    real_ip_header              X-Forwarded-For;
    real_ip_recursive           on;

    server {
        listen 4080;
        server_name localhost;
        root /htdocs;
        autoindex on;
        client_max_body_size 512m;
        index index.php;

        location / {

            try_files $uri $uri/ /index.php;

            location = /index.php {
                fastcgi_pass      127.0.0.1:9000;
                fastcgi_index     index.php;
                include           fastcgi.conf;
            }

            location = /instashow/api/index.php {
                fastcgi_pass      127.0.0.1:9000;
                fastcgi_index     index.php;
                include           fastcgi.conf;
            }
        }

        location ~ \.php$ {
            return 444;
        }
    }
}

Any clue?

EDIT:

Forgot to mention I've also tried to set all tables, columns and fields to utf8mb4_unicode_ci and then also "restored and optimized" the database.

EDIT 2:

I've also seen this question before posting mine:
UTF-8 all the way through

And that's why I decided to post it anyway. I've tried all solutions and recommendations of it and I'm still at the same spot. I do have a reverse proxy in front of it (another NGINX), can that be somehow related?

EDIT 3:

I'm able to insert accented characters to the database manually via PhpMyAdmin, they're stored just fine. Also the CodeIgniter app is able to read and display them with no problem. The issue is when it comes to writing it to the database via the app.

It must be something unrelated to the database I think, it might be something with my PHP setup, I don't know. Does PHP-FPM affects any of it? I know that the "default charset" configuration for the PHP is also set to UTF-8.

EDIT 4:

The name gets saved as "". (Empty string)

This is wrong. It gets saved as NULL.

  • 写回答

0条回答 默认 最新

    报告相同问题?

    悬赏问题

    • ¥15 DIFY API Endpoint 问题。
    • ¥20 sub地址DHCP问题
    • ¥15 delta降尺度计算的一些细节,有偿
    • ¥15 Arduino红外遥控代码有问题
    • ¥15 数值计算离散正交多项式
    • ¥30 数值计算均差系数编程
    • ¥15 redis-full-check比较 两个集群的数据出错
    • ¥15 Matlab编程问题
    • ¥15 训练的多模态特征融合模型准确度很低怎么办
    • ¥15 kylin启动报错log4j类冲突