Character set 'utf8mb3' not supported error

dynasoft

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem: Hi, I keep getting the following error trying to post a simple numeric string to a VARCHAR field in msql:
Character set 'utf8mb3' is not supported by .Net Framework
Script for creating the field is:

CREATE TABLE mytable (
  UserName varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  Password varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,  
  PRIMARY KEY (ID),
  KEY Index_UserName (UserName) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=618 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Error is triggered when i save a number instead of a string in userName. Password is not giving the issue

This is how i save usernames in my code:

editor.Field(new Field("mytable.UserName")
    .Xss(false)
    .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(CommonUtilities.ToString(val).Trim()) == true ? null : CommonUtilities.ToString(val).Trim())
    .Validator(Validation.Unique(new ValidationOpts
    {
        Message = lblo.lblUserNameHasAlreadyBeenTaken
    }))
    .SetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? null : HttpUtility.HtmlDecode(CommonUtilities.ToString(val)))
);

Method CommonUtilities.ToString just converts to string
I have tried doing a conversion to string in initSubmit event:

var userName = this.field('mydb.UserName').val();
if (IsNumeric1(userName)) {
    this.field('mydb.UserName').val(ToString(userName));  // Ensure numeric values are treated as strings
}

and even added a trigger in the db all to no avail:

CREATE TRIGGER `convert_username_to_string`
BEFORE INSERT ON `mydb`.`mytable`
FOR EACH ROW
SET NEW.UserName = CAST(NEW.UserName AS CHAR);

The issue isn't with Mysql or the fact that I index the UserName as I have this elswhere with other tables and fields and saving numerics is fine. Please advise.

dynasoft

The db is also CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

dynasoft

Db connection string I use is this:

Server=localhost;Database=MyDB;Uid=xxx;Pwd=yyy;AllowLoadLocalInfile=true;Integrated Security=false;Pooling=true;Min Pool Size=5;Max Pool Size=100;Connection Lifetime=0;Connect Timeout=15;Connection Reset=true;

Inserting the following works fine too from the mysql editor:

INSERT INTO xxx.yyy (UserName, Password) VALUES ('12345', 'testpassword');

allan

Hi,

What version of the MySQL Connector for .NET are you using? I found this SO question that has basically the same issue, and it suggests that by upgrading the Nuget package, it should hopefully fix it.

Allan

dynasoft

We use 8.0.27.0 but can't use any other because of other assemblies that use this particular version

dynasoft

And also MySqlConnector 2.0.0.0

allan

Oof - 2.0.28 is the version that includes the fix! Are your other dependencies hard fixed to .27, or can they go up a patch level?

Allan

dynasoft

Thanks Allan. We have added a check for any numeric values when users select a username and added this is a todo item for the next release. Thanks again.

dynasoft

The other assemblies that use version 8.0.27 are Sage....We'll have to wait, check and see what happens there.