0

For some reason, I can't create the employeeTable on the sample database when opening the SamplePage.php on my Chrome browser from the following link:

http://ec2-13-57-28-240.us-west-1.compute.amazonaws.com/SamplePage.php

I was able to create my EC2 Instance from the AWS Create an EC2 Instance and Install a Web Server tutorial on the following link:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Tutorials.WebServerDB.CreateWebServer.html

I get the following error:

Failed to connect to MySQL: Server sent charset unknown to the client. Please, report to the developers.

From what I read, the AWS Linux AMI image has a UTF-8 character set by default and I get the output running this command on my EC2 Instance after connecting to my MySQL DB Instance:

SHOW VARIABLES LIKE 'char%';

  Variable_name            | Value                                     
|+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | utf8mb4                                   |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8                                      |
| character_set_server     | utf8mb4                                   |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-8.0.17.R1/share/charsets/ |

I also set my Putty /.bash_profile to use the UTF-8 character set, by inputting the configuration as:

export LC_CTYPE="en_US.UTF-8"

Not sure what is going on as I as, I believe, I followed the complete tutorial on how to create a DB Instance and link to EC2 Instance from this link:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/TUT_WebAppWithRDS.html

Chris Williams
  • 32,215
  • 4
  • 30
  • 68
sonicspark
  • 25
  • 1
  • 1
  • 10
  • What versions of the various components (clients, server, etc)? Did you recently change cloud services? – Rick James Jul 19 '20 at 19:18
  • My client is Windows 10. The AWS EC2 Instance is Linux AMI version 1 and DB instance is MySQL 8.0.17. This is my first experience with any cloud services. – sonicspark Jul 19 '20 at 19:25
  • I do notice that the character_set_client, character_set_connection, charcter_set_results, and the character_set_system are utf8, while the character_set_database and character_set_server are utf8mb4. I don't know if this makes a difference? – sonicspark Jul 19 '20 at 19:37
  • I found an article: that may be useful, but I am not experienced enough to interpret it, sorry. – sonicspark Jul 19 '20 at 19:39
  • Can someone guide me on how to change my character_set_database value to UTF8 and my character_set_server value to UTF8, to see if that may fix the issue? – sonicspark Jul 19 '20 at 20:17
  • 1
    Unless you have some particular need for "utf8mb3", you should use "utf8mb4". – Rick James Jul 20 '20 at 05:17

4 Answers4

1

Turns out all this work on changing the charset and making sure the charset in the database was set properly was just a dependency on another problem, which was the mysqli extension was not properly installed and enabled. I also upgrade the php version to 7.2, which also may have fixed the issue but I am not entirely sure. Maybe someone can verify what the fix was based on what I had done?

sonicspark
  • 25
  • 1
  • 1
  • 10
1

I solved this by removing php56 and installing php72 instead. Following are the commands

sudo yum -y remove php*

sudo yum install php72

sudo yum install php72-mysqlnd

sudo apachectl restart

gunwin
  • 4,578
  • 5
  • 37
  • 59
Alok Agrawal
  • 11
  • 1
0

It depends.

Have you have not put any text into the tables yet, then:

ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4;

If you have data in the table, there are about 3 different ways to change the charset. The tips are here:
http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases

If you have garbage coming out, then see Trouble with UTF-8 characters; what I see is not what I stored

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I tried to run the command `ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4;` but got the following error: `ERROR 1046 (3D000): No database selected` – sonicspark Jul 20 '20 at 01:15
  • I inputted `USE sample;` then I ran `ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4;` I got the following error: `ERROR 1146 (42S02): Table 'sample.t' doesn't exist`. The error is correct, the table does not exist. The `SamplePage.php` is written to create the `employeeTable` but due to the charset on the `sample` database being different (i.e. UTF-8 compared to UTF8mb4) I am not able to create the `employeeTable`. Just want to create the `sample` database with the `employeeTable` like the `SamplePage.php` was designed to do. Sorry, I wasn't clear, earlier. – sonicspark Jul 20 '20 at 01:25
  • Also, is it possible to change the sample database to be a utf-8 character set instead of a utf8mb4? Is there a command to change it? Maybe, this will solve the charset unknown problem. – sonicspark Jul 20 '20 at 01:31
  • I was able to set the `character_set_server` and the `character_set_database` to `utf8mb3` or `utf8`, by running the `SET NAMES 'utf8mb3';` command and the `set character_set_server ='utf8mb3';` command, but I still get the error`Failed to connect to MySQL: Server sent charset unknown to the client. Please, report to the developers Error creating table.` Maybe, I have to do something to the `SamplePage.php` file to get the char set correct. – sonicspark Jul 20 '20 at 01:48
  • I placed the header `header("Content-Type: text/html;charset=UTF-8");` into the `SamplePage.php` and still get the same error. I think I have run out of ideas, except to create the database again to see if I may have missed something? – sonicspark Jul 20 '20 at 02:40
0

To solve the issue, remove PHP 7.4 and replace with 7.2

sudo yum -y remove php*
sudo amazon-linux-extras disable php7.4
sudo amazon-linux-extras enable php7.2
yum clean metadata
sudo yum install php php-{pear,cgi,common,curl,mbstring,gd,mysqlnd,gettext,bcmath,json,xml,fpm,intl,zip,imap}
php -v
sudo apachectl restart
Sven Eberth
  • 3,057
  • 12
  • 24
  • 29
Edson
  • 9
  • 1