Ecto ERROR 22001 (string_data_right_truncation) when I have validate_length

Questions / Help
1

Hello, I created a migration like this:

add :seo_description, :string, size: 164, null: false

and my schema file:

field :seo_description, :string, size: 164, null: false

and my changeset:

|> validate_length(:seo_description, max: 164)

but when I import string more than 164 like 166 I have this error:

** (exit) an exception was raised:
    ** (Postgrex.Error) ERROR 22001 (string_data_right_truncation) value too long for type character varying(164)
        (ecto_sql) lib/ecto/adapters/sql.ex:621: Ecto.Adapters.SQL.raise_sql_call_error/1

I have written changeset validate_length but I don’t know why this has this error!!

my Ecto:

{:phoenix_ecto, "~> 4.0"},
{:ecto_sql, "~> 3.1"},

and my elixir ver: v1.8.0

how can I fix this ? thanks

2

How are you validating and writing your data to the DB?

And please tell us the version of ecto and ecto_sql according to your mix.lock.

3

Hello, I wrote my migration on top and my mix file ver that I called on top post is

{:phoenix_ecto, "~> 4.0"},
 {:ecto_sql, "~> 3.1"},

my migration:

defmodule Khatoghalam.Repo.Migrations.Blogs do
  use Ecto.Migration
  @disable_ddl_transaction true

  def change do
    create table(:blogs, primary_key: false) do
      add :id, :uuid, primary_key: true

      add :title, :string, size: 150, null: false
      add :short_description, :text, null: false
      add :description, :text, null: false
      add :seo_alias_link, :string, size: 200, null: false
      add :seo_words, :string, size: 164, null: false
      add :seo_description, :string, size: 164, null: false
      add :image, :string, size: 200, null: false
      add :tags, :string, size: 164, null: false

      add :status, :boolean, null: false
      add :category_id, references(:categories, on_delete: :nothing, type: :uuid)
      add :user_id, references(:users, on_delete: :nothing, type: :uuid)
      timestamps()
    end
    create(
      index(:blogs, [:seo_alias_link],
        # concurrently: true,
        name: :unique_index_on_blogs_alias_link,
        unique: true
      )
    )
  end
end

do you need more info ?

add :seo_description, :string, size: 164, null: false

its size on database is size: 164 and in my psql

21%20am

I think it has problem with utf8 character like Persian language maybe!!?

my full schema file:

defmodule Khatoghalam.Admin.Blogs.BlogSchema do
  use Ecto.Schema

  import Ecto.Changeset
  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id

    schema "blogs" do

    field :title, :string, size: 150, null: false
    field :short_description, :string, size: 300, null: false
    field :description, :string, null: false
    field :seo_alias_link, :string, size: 200, null: false
    field :seo_words, :string, size: 164, null: false
    field :seo_description, :string, size: 164, null: false
    field :image, :string, size: 200, null: false
    field :status, :boolean, null: false
    field :tags, :string, size: 164, null: false


    belongs_to :blog_categories, Khatoghalam.Admin.Blogs.CategorySchema, foreign_key: :category_id, type: :binary_id
    belongs_to :users, Khatoghalam.Admin.Users.UserSchema, foreign_key: :user_id, type: :binary_id
    timestamps()

    end

    @all_fields ~w(title short_description description seo_alias_link seo_words seo_description image status category_id tags user_id)a
    def changeset(struct, params \\ %{}) do
      struct
      |> cast(params, @all_fields)
      |> validate_required(@all_fields, message: "فیلد مذکور نمی تواند خالی باشد.")
      |> validate_length(:title, max: 150, message: "حداکثر ۱۵۰ کاراکتر")
      |> validate_length(:short_description, max: 300, message: "حداکثر ۳۰۰ کاراکتر")
      |> validate_length(:seo_alias_link, max: 200, message: "حداکثر ۲۰۰ کاراکتر")
      |> validate_length(:seo_words, max: 164, message: "حداکثر ۱۶۴ کاراکتر")
      |> validate_length(:seo_description, max: 164, message: "حداکثر ۱۶۴ کاراکتر")
      |> validate_length(:image, max: 200, message: "حداکثر ۲۰۰ کاراکتر")
      |> validate_length(:tags, max: 164, message: "حداکثر ۱۶۴ کاراکتر")
      |> foreign_key_constraint(:category_id)
      |> foreign_key_constraint(:user_id)
      |> unique_constraint(:seo_alias_link, name: :unique_index_on_blogs_alias_link, message: "لینک مطلب تکراری می باشد باید یک لینک یکتا بسازید.")
    end
  end
4

by the way I see the same problem on other section. I write a text on google translate and it counts then give me a number like 165 but my change set validate is 164, now it is saved on my db!!

the filed I have an error , when I write more than 200 charcter it shows me a error but 165 not

5

You still haven’t shown how you actually insert and validate the data that causes the problem…

So please check if you use UTF8 as the tables encoding, psql -l will tell you.

1 Like
6

okay sorry my bad English is not my native language,

my insert code is:

def create_post(attrs) do
      add = %BlogSchema{}
      |> BlogSchema.changeset(attrs)
      |> Repo.insert()
      case add do
        {:ok, post_info}    -> {:ok, :create_post, post_info}
        {:error, changeset} -> {:error, :create_post, changeset}
      end
    end

I always write a changeset and valid my input. and works for me , but I don’t know it doesn’t work ,

 Name          |  Owner   | Encoding | Collate | Ctype |   Access privileges
 khatoghalam_dev       | postgres | UTF8     | C       | C     |

these are every thing I used in my code , changeset and create table with ecto migration

7

it is interesting I think ecto changeset has problem with Persian character, because I uses English word. it has no error

8

Try counting :codepoints rather than :graphemes:

  • :count - what length to count for string, :graphemes (default), :codepoints or :bytes
2 Likes
10

I think it was fixed by your help

      |> validate_length(:seo_description, max: 164, message: "حداکثر ۱۶۴ کاراکتر", count: :codepoints)

I change it to codepoints I update this page if i have error with it

Thank you