Ecto says data is too long for VARCHAR field, but it isn't (myxql)

Phoenix Forum Questions / Help
,
1

Inserting with Repo.insert() I get this error:

[debug] QUERY ERROR db=45.5ms queue=5.9ms
INSERT INTO `movies` (`id_nf`,`title`,`year`,`inserted_at`,`updated_at`) VALUES (?,?,?,?,?) [6483, <<83, 109, 111, 107, 101, 121, 32, 74, 111, 101, 39, 115, 32, 67, 97, 102, 233>>, 2001, ~N[2019-08-01 10:52:13], ~N[2019-08-01 10:52:13]]
** (MyXQL.Error) (1406) (ER_DATA_TOO_LONG) Data too long for column 'title' at row 1
    (ecto_sql) lib/ecto/adapters/myxql.ex:208: Ecto.Adapters.MyXQL.insert/6
    (ecto) lib/ecto/repo/schema.ex:649: Ecto.Repo.Schema.apply/4
    (ecto) lib/ecto/repo/schema.ex:262: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    (mvsg_ex) lib/scripts.ex:31: anonymous fn/2 in Mvsg.Scripts.load_nf/0
    (elixir) lib/enum.ex:783: Enum."-each/2-lists^foreach/1-0-"/2
    (elixir) lib/enum.ex:783: Enum.each/2
    (mvsg_ex) lib/scripts.ex:27: Mvsg.Scripts.load_nf/0
iex(3)> IO.puts [83, 109, 111, 107, 101, 121, 32, 74, 111, 101, 39, 115, 32, 67, 97, 102, 233]
Smokey Joe's Café

Which sounds like it is saying the text for the title (“Smokey Joe’s Café”) is too long, but the field is VARCHAR(255) so it should fit. In fact I am able to insert it with the following pure SQL query:

INSERT INTO `movies` (`id_nf`,`title`,`year`,`inserted_at`,`updated_at`) VALUES (6483, "Smokey Joe's Café", 2001, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)

(Copied and edited from the error message)

Is this a bug? What can I do other than inserting with raw sql when this happens?

2

Yes, that’s a myxql bug, I’m able to reproduce this locally - thank you for the report.

I did notice one thing:

binary = <<83, 109, 111, 107, 101, 121, 32, 74, 111, 101, 39, 115, 32, 67, 97, 102, 233>>
<<83, 109, 111, 107, 101, 121, 32, 74, 111, 101, 39, 115, 32, 67, 97, 102, 233>>
iex> :unicode.characters_to_binary(binary, :latin1, :unicode)
"Smokey Joe's Café"

it looks like your input is latin1-encoded and not in utf8. Where do you get the data from? What’s the charset set on the table you’re inserting to? When the input is utf8 encoded and the table has utf8 encoding it works well for me.

2 Likes
3

As an alternative you might be able to use :text instead of :string for your DB column type

1 Like
4

Thanks, the database table is using utf8. I was getting the data with File.stream!(@filename). While researching how to encode the input data as latin1, I came across this thread: wtUTF8 - encoding issues reading CSV file. Just replacing File.stream!(@filename) with File.stream!(@filename, [encoding: :latin1]) fixed it.

5

Is the table utf8 or utf8_mb4? I think MySQL has some issues with the former

1 Like
6

One could say that utf8 in mysql isn’t utf8 at all. It’s just using up to 3 bytes not the needed 4. utf8_mb4 does remidy that.

1 Like
7

Thanks for the heads up, the table uses utf8. After reading about it, it seems like a good idea to change that. Although I don’t expect that the table will need 4-byte characters, as it only keeps movie titles.