Good point, and one that is echoed here: Odd Binary ID Mismatch
I did find that the table was encoded with latin1, so I did this:
ALTER TABLE replication_types CONVERT TO CHARACTER SET utf8;
I tried again. Same results, unfortunately.
cboebel
al2o3cr
3f in ASCII is ?. Looks like MySQL will replace characters that aren’t representable in the column’s character set with ?:
1
isaac-rstor
don’t forget to also change the collation, try running this:
ALTER TABLE `replication_types` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci
1
cboebel
I tried this (thank you for the suggestion) and received the same results.
Here’s what I don’t understand about these suggestions: This is a binary field (binary(16)). Why would things like collation and character set have any bearing on what’s stored in a binary field? I should be able to store anything in there, right? An image, for example. (Not that I would).
0
cboebel
I agree it looks fishy, but I don’t understand why it would replace characters in a binary field. I should be able to put anything in there, right?
0
axelson
Scenic Core Team
Yeah, I would expect that as well, but that doesn’t necessarily mean it is actually true in reality.
Can you show the output of SHOW FULL COLUMNS FROM table_name;? Although I’m not sure if we’d learn anything else beyond binary(16). Or if you could create a sample project that reproduces the issue, that would be helpful in tracking it down.
0
isaac-rstor
I’m not 100% sure but it probably has to do with the ecto adapter. I think SQL’s put binary data into themselves using, say, stdin and if your adapter is sending non-texty data on stdin, strange things happen if the encodings don’t match. Quite a bit of an SQL query must be in text, so mix &match is bound to be hairy, e.g. how POST http queries can get strange esp. with multipart form encoding
0
cboebel
Here’s the output from show full columns:
+-------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| id | binary(16) | NULL | NO | PRI | NULL | | select,insert,update,references | |
| name | varchar(255) | latin1_swedish_ci | YES | UNI | NULL | | select,insert,update,references | |
| inserted_at | datetime | NULL | NO | | NULL | | select,insert,update,references | |
| updated_at | datetime | NULL | NO | | NULL | | select,insert,update,references | |
+-------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
I’ll do a small demonstration project later today and put it up somewhere.
Thank you for your help.
1
isaac-rstor
Apparently you might want to try utf8mb4
Also try the settings in /etc/my.cnf, I suspect especially the client one might be important.
0
Rainer
Hi,
I’m having a similar issue with mysql and uuid, but can’t find a solution.
Creating and reading an entry works in about 80% of the time, but in the other cases the id in the database will be different than the one in the changeset.
i wrote a simple test that inserts and then reads again like this:
def failtest(conn, _params) do
insert(conn, 0) |> index(nil)
end
def insert(conn, count)do
changeset = Picture.changeset(%Picture{}, %{name: "test"<>Integer.to_string(count)})
Repo.insert(changeset)
|> case do
{:ok, picture} ->
cond do
Repo.one(from p in Picture, where: p.id == ^picture.id) ->
if count < 100 do
insert(conn, count+1)
else
conn |> put_flash(:info, "finished")
end
true ->
conn |> put_flash(:error, "couldn't find entry")
end
{:error, _param} ->
conn |> put_flash(:error, "couldn't insert")
end
end
Usually it will abort somewhere between 2 and 15 entries created.
Does someone have an idea about that?
0