SQL to NOCODB : encoding issue

Joachim_Brindeau · December 2, 2024, 7:07am

Hello everyone,

I have a big postgres database containing several thousand rows I want to transfer to nocodb’s native database system.

However, for a lots of rows and for different fields, I get the following error:

... - Incorrect string value: '\xE2\x89\xA4 2 ...' for column 'beneficiaires' at row 1

I understand it’s an encoding issues but all the function nodes I tried to build with chatgpt failed. I need the HTML to remain “functional” because I have a sync between NocoDB and Wordpress.

Here is the workflow with some sample data:

I am running v1.70.1 on Docker, managed via Portainer on a local Ubuntu Server.

I hope someone can help me out!
Thank you very much,
Joachim

n8n · December 2, 2024, 7:07am

It looks like your topic is missing some important information. Could you provide the following if applicable.

n8n version:
Database (default: SQLite):
n8n EXECUTIONS_PROCESS setting (default: own, main):
Running n8n via (Docker, npm, n8n cloud, desktop app):
Operating system:

Joachim_Brindeau · December 6, 2024, 6:28am

Hello,
It’s me again.

Searching more, I found this post:

Which brought me to this:

github.com/nocodb/nocodb

utf8mb4 character set instead of utf8

opened 02:13PM - 23 Feb 22 UTC

closed 05:27AM - 10 Mar 22 UTC

Pierric82

## Force utf8mb4 character set in MariaDB tables **Need for this feature** A… typical nocoDB + mariaDB installation will see insertions fail (and the API return an error) when trying to insert some subset of Unicode characters (4-byte UTF-8 characters). Although the root issue might be considered to be on MariaDB side (since 2013?!), this proposal would solve it. **Is your feature request related to a problem? Please describe.** By default MariaDB uses `utf8` as character encoding. In MariaDB, this set supports unicode characters encoded in 3 bytes such as ⏪, but not those encoded on 4 bytes such as 🎪. When inserting such characters (at least through the API bulk creation), an error is returned, because MariaDB complains about `Incorrect string value: '\xF0\x9F\x8E\xAA \xE2...' for column d1.nc_2fmg__testemails.subject at row 1"`. **Describe the solution you'd like** Although there might be options to set up MariaDB with the correct encoding `utf8mb4`, I think a lot of people would stumble upon this issue when trying out nocodb + mariadb. I'm proposing to include functionality in nocodb to force the creation of new tables with that character set. I see 2 ways: - option 1: with an environment variable at the Docker container level - option 2: within the GUI, as a checkbox when creating a table In either case, I would strongly recommend making the option selected by default (i.e., use the `utf8mb4` character set). **Describe alternatives you've considered** One could argue action should be taken on the MariaDB front directly. I haven't explored that direction, but [the article](https://karlssonondatabases.blogspot.com/2013/09/using-4-byte-utf-8-aka-3-byte-unicode.html) that I found that got me to understand what was happening goes back to... 2013. So I wouldn't count on a quick fix there. Alternatively, manually setting up MariaDB, either by adding ```text [mysqld] character-set-server = utf8mb4 ``` to the MariaDB configuration (untested), or by converting the database to the character set through ```sql alter database your_db_name character set = 'utf8mb4'; ``` in a mysql client can be done, but would need to be documented somewhere for future NocoDB onboarders. When faced with the issue for the first time without any idea of what is going on, it's hard to link it to character encoding, so I think that a default option as proposed will be better for many people (also, people installing nocodb might not want to install a mysql client). **Additional context** For reference, to fix the issue on existing tables, one can convert tables with ```sql alter table nc_2fmg__tablename convert to character set 'utf8mb4'; ``` whereby 2fmg is the suffix in the project id. One should also convert the database as mentioned in the previous section, to ensure future tables don't have the issue. Additional source that helped me: [article on character sets in MariaDB](https://mariadb.com/kb/en/setting-character-sets-and-collations/).

I will keep investigating because I am not sure I understand the discussion

Pierric · December 6, 2024, 11:24am

Hi,

The gist of those other conversations is that with the default installation of MariaDB, the database was set up in such a way that some unicode characters were not accepted and caused a crash.

In my case it did accept utf8 characters encoded on 3 bytes, but not utf8 characters encoded on 4 bytes, because of how MariaDB considers to be utf8 (vs the so-called utf8mb4, a superset of utf8). If you look closely you’ll see my error message was similar to yours but had 4 bytes grouped together. Your error shows only 3 bytes. This tells me the situation is not exactly identical; you might be getting an error on a character that would be accepted as utf8. Maybe your database is set up for some encoding that is not even utf8-compatible? this is a bit surprising as i believe utf8 was the default encoding when I installed mariaDB - though I could be misremembering.

Playing with the encoding commands in mariaDB to determine what encoding your database and tables are using (you’ll want to check those you’re trying to insert data into) might put you on the right track.

Disclaimer: I’ve not been looking at this closely since those original conversations, I’m assuming nothing has fundamentally changed since then.

Joachim_Brindeau · December 6, 2024, 11:53am

Thank you @Pierric, I fed all the conversations to chatgpt and it gave me a bunch of commands to move all my existing tables to the right encoding and to set it for future tables. It is solved

Thank you for pointing me in the right direction!!

system · December 13, 2024, 11:53am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.