How to easily change the charset of your database tables with Wappler (to save emojis, and more!)

Hello there :wave:

Here is a little tutorial to show you how you can easily change the charset of your database and your tables by using the terminal available in Wappler.
Actually you can use the same method to connect to your database and do more things than just converting the charset to another one!

In this example, we use Docker and we want to be able to save emojis in our database. If your database table is not using the right charset (utf8mb4), it won’t work and you’ll certainly see this error: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

First of all, we’ll need to connect to our database server.
At the bottom right of the Wappler interface, click on “terminal”, then choose “SQL Shell”, or you can use “Database Server” as following :
wappler-charset

The terminal will show up with something like “root@fbdc784ac3ee:/#” at the beginning.

Now, we need to login to the database server, to do this simply enter this text (you can copy and past it): mysql -u root -p

The system will ask you the root password:

wappler-charset-2
No worries, you can simply find it in your project settings:


Past your password and hit Enter!

Once logged in, you’ll see a welcome message:

The next step will be to select your database, if you’re not sure about its correct name, enter this command: SHOW DATABASES;
Then you’ll see your database name, in my case here it’s cadebme:
wappler-charset-5

In order to change something in this database, we need to select it, to do so, enter this command: use MYDATABASE; (change MYDATABASE by your database name you found just before):
wappler-charset-6

Now we’ll do two different things, the first one is to convert the database to the new charset and the second one is to convert the different tables where we want to use the new charset (and being able to save emojis)

  1. Convert the database charset.
    Entre the following command and replace mydatabase by your database name you found earlier: ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
    Press Enter and you should see a success message like Query OK, 1 row affected (0.010 sec)

  2. Convert your tables
    First of all, we’ll check the current charset of our table (you know the table name because you’ve created them in the Visual database Manager directly in Wappler)
    wappler-charset-7

Enter the following command to see the current charset of your table, replace Users by your table name: SHOW FULL COLUMNS FROM Users;
Here is the result of this command:


As you can see, the current charset (collation) used for the rows in this table is latin1_swedish_ci, but we don’t want that, we want to convert it to utf8mb4 to be able to save emojis.

So now, all you need to do is enter this command to convert to utf8mb4, replace Users by the name of your table: ALTER TABLE Users CONVERT TO CHARACTER SET utf8mb4;

Press Enter and you should see a success message like this:
Query OK, 1 row affected (0.040 sec)
Records: 1 Duplicates: 0 Warnings: 0

And voilà! :tada:
Just to be sure, reuse the command we used before to check the current charset used in this table (SHOW FULL COLUMNS FROM Users;), you should see the new charset, like me:

Now you can simply enter exit to close the connection to the database server.

The final step is to set the charset in the db settings in Wappler directly as following.

Now just save, deploy and it should work! :slight_smile:

wappler-charset-how-to-result

9 Likes

I have been attempting this tutorial but I get a error after the following command

This is the error

    ALTER DATABASE elegant-craft CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-craft CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci' at line 1         

I believe this is down to my database name containing a - can I safely rename my database or will I end up losing schema or data etc?

@George any chance you could confirm the above whenever you get a chance would really like to get emojis working and believe the database name is the issue don’t want to rename if I lose schema

Commands should be on a single line ending with ;

This was the command I used it was all on the same line and ended with;

Maybe im making a mistake new to this was unsure as the error said near -craft which is the second half of my database name

Just incase anyone else attempts this with a already made schema and has a - in their database name

This command
ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

might not work like this for you in my case I had to edit the syntax to have my database name wrapped in `

e.g:

ALTER DATABASE `my-database` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

1 Like