Character Set Configuration of PostgreSQL mysql_fdw

This article addresses one question, What can I do if unreadable code appears because the character set of MySQL is different from that of PostgreSQL?

By digoal

Background

What can I do if unreadable code appears because the character set of MySQL is different from the character set of PostgreSQL?

Check the mysql_fdw source code. The options for mysql_fdw are listed below:

#define CR_NO_ERROR 0  
/*  
 * Options structure to store the MySQL  
 * server information  
 */  
typedef struct mysql_opt  
{  
    int           svr_port;               /* MySQL port number */  
    char          *svr_address;           /* MySQL server ip address */  
    char          *svr_username;          /* MySQL user name */  
    char          *svr_password;          /* MySQL password */  
    char          *svr_database;          /* MySQL database name */  
    char          *svr_table;             /* MySQL table name */  
    bool          svr_sa;                 /* MySQL secure authentication */  
    char          *svr_init_command;      /* MySQL SQL statement to execute when connecting to the MySQL server. */  
    unsigned long max_blob_size;          /* Max blob size to read without truncation */  
    bool          use_remote_estimate;    /* use remote estimate for rows */  
      
    // SSL parameters; unused options may be given as NULL  
    char          *ssl_key;               /* MySQL SSL: path to the key file */  
    char          *ssl_cert;              /* MySQL SSL: path to the certificate file */  
    char          *ssl_ca;                /* MySQL SSL: path to the certificate authority file */  
    char          *ssl_capath;            /* MySQL SSL: path to a directory that contains trusted SSL CA certificates in PEM format */  
    char          *ssl_cipher;            /* MySQL SSL: list of permissible ciphers to use for SSL encryption */  
} mysql_opt;

svr_init_command indicates that an SQL request (like the set connection variable) can be executed when PostgreSQL connects to MySQL using mysql_fdw.

You can set the init_command option to set the character set of the MySQL client to enable automatic transcoding.

https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html

As described above, it is still necessary for applications to configure their connection using SET NAMES or equivalent after they connect? You might be tempted to start the server with the --init_connect="SET NAMES 'utf8'" option to cause SET NAMES to be executed automatically for each client that connects. However, this may yield inconsistent results because the init_connect value is not executed for users that have the SUPER privilege.

0 0 0

Share on

: digoal

282 posts | 24 followers
Follow

Community

Character Set Configuration of PostgreSQL mysql_fdw

Background

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

PolarDB for MySQL

AnalyticDB for PostgreSQL

AnalyticDB for MySQL