Character set 'utf8mb3' not supported error

Character set 'utf8mb3' not supported error

dynasoftdynasoft Posts: 446Questions: 69Answers: 3
edited October 13 in DataTables 1.10

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.

Answers

  • dynasoftdynasoft Posts: 446Questions: 69Answers: 3

    The db is also CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

  • dynasoftdynasoft Posts: 446Questions: 69Answers: 3

    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');

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    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

  • dynasoftdynasoft Posts: 446Questions: 69Answers: 3

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

  • dynasoftdynasoft Posts: 446Questions: 69Answers: 3

    And also MySqlConnector 2.0.0.0

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    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

  • dynasoftdynasoft Posts: 446Questions: 69Answers: 3

    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.

  • dynasoftdynasoft Posts: 446Questions: 69Answers: 3

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

Sign In or Register to comment.