mysql update off by one character

Question

I am using mysql and in the table 'items' updates on the variable image_url 'succeed' with no warnings. But, in reality, the update is failing: it prepends the value with a space and deletes the last character of the value I give it.

Here is the update:

UPDATE items
    SET image_url = 'http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jpg'
    WHERE id=38;

Here is the select:

select * from items\G;

Here is one line of the output:

...
image_url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jp
...

notice the missing 'g' at the end and the extra space at the beginning.

How do I stop this?

Here is some system info that may help:

mysql> show variables LIKE '%version%';
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.5.46                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| version                 | 5.5.46-0ubuntu0.14.04.2 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | i686                    |
| version_compile_os      | debian-linux-gnu        |
+-------------------------+-------------------------+
7 rows in set (0.00 sec)

EDIT 1 Table description:

mysql> desc items;

+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
...
| image_url   | varchar(255)  | NO   |     | NULL    |                |
...

EDIT 2 Checking for triggers:

mysql> show triggers \G
Empty set (0.00 sec)

EDIT 3 Another example:

I am doing all these commands from command line. Another example:

UPDATE items SET image_url = 'http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jpg33333333333333' WHERE id=38;

select * from items\G;

...
image_url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jpg3333333333333
...

EDIT 4 Checking length of inputs and outputs:

mysql> select image_url,length(image_url) from items where id=38\G;
*************************** 1. row ***************************
        image_url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jp
length(image_url): 61
1 row in set (0.00 sec)

http://www.lettercount.com/ gives http://ecx.images-amazon.com/images/I/61Dz5t8wjQL.SX522.jpg 61 characters as well, which makes sense given that the update is not changing the length of the string, just deleting the last characters and adding a space to the beginning,

EDIT 5 Trying encoding:

base64 encoding: aHR0cDovL2VjeC5pbWFnZXMtYW1hem9uLmNvbS9pbWFnZXMvSS82MUR6NXQ4d2pRTC5fU1g1MjJfLmpwZw==

mysql> UPDATE items SET image_url = 'aHR0cDovL2VjeC5pbWFnZXMtYW1hem9uLmNvbS9pbWFnZXMvSS82MUR6NXQ4d2pRTC5fU1g1MjJfLmpwZw==' WHERE id=38;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select image_url,length(image_url) from items where id=38\G;
*************************** 1. row ***************************
        image_url:  aHR0cDovL2VjeC5pbWFnZXMtYW1hem9uLmNvbS9pbWFnZXMvSS82MUR6NXQ4d2pRTC5fU1g1MjJfLmpwZw=
length(image_url): 84
1 row in set (0.00 sec)

decoding: aHR0cDovL2VjeC5pbWFnZXMtYW1hem9uLmNvbS9pbWFnZXMvSS82MUR6NXQ4d2pRTC5fU1g1MjJfLmpwZw=

gives: http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jpg

EDIT 6 Checking if Insert fails as well:

mysql> INSERT INTO items (url, image_url) VALUES('http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jpg', 'http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jpg');
Query OK, 1 row affected, 2 warnings (0.03 sec)

the warnings are because I did not give all the values where NULL:NO values in this insert

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1364 | Field 'created_at' doesn't have a default value |
| Warning | 1364 | Field 'updated_at' doesn't have a default value |
+---------+------+-------------------------------------------------+
2 rows in set (0.00 sec)


mysql> select image_url,length(image_url),url from items where id=39\G;
*************************** 1. row ***************************
        image_url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jp
length(image_url): 61
              url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jp
1 row in set (0.00 sec)

So, it also fails on insert.

EDIT 7 create table information

mysql> show create table items\G;
*************************** 1. row ***************************
       Table: items
Create Table: CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  ...
  `image_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `color` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  ...
  `store` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_items_on_id` (`id`),
  KEY `index_items_on_url` (`url`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

EDIT 8 More table and column information

select * from information_schema.columns where table_name='items' and column_name='image_url'\G;

*************************** 2. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: development_database
              TABLE_NAME: items
             COLUMN_NAME: image_url
        ORDINAL_POSITION: 5
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 255
  CHARACTER_OCTET_LENGTH: 765
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: utf8
          COLLATION_NAME: utf8_unicode_ci
             COLUMN_TYPE: varchar(255)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
2 rows in set (0.01 sec)

ERROR: 
No query specified

EDIT 9 Charlength readouts

mysql> select image_url,length(image_url),char_length(image_url),url from items where id=39\G;
*************************** 1. row ***************************
             image_url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jp
     length(image_url): 61
char_length(image_url): 61
                   url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jp
1 row in set (0.00 sec)

ERROR: 
No query specified

EDIT 10 showing variables like character

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

EDIT 11: THE POTENTIAL ISSUE

The error does not appear in the users table, but it does occur in the items table. Here is the difference that I think may be causing the issue. (I do not yet have a solution since the item table has that UTF-8 for a reason: urls can have some funky characters)

show create table users\G;

ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

show create table items\G;

ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

To be honest I think this should be a Community Answer, as I was a little later on the scene and others had done some important ground work establishing what was and was not a factor in this issue.

This link may be relevant, as your table character set is utf8 so the last character in the string may be getting skewed (and not saving correctly, thus disappearing).

All of the rows in EDIT 10 which reference latin1 or utf8 character set collations should be the same, and ideally should be utf8mb4 . I would now hazard a guess that the saving of UTF-8 characters in a non-true-utf-8 character collation is meaning the final character of any string is an incomplete reference and so not displaying.

So to solve your issue run the command:

ALTER TABLE items CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

---

For info / background:

utf8mb4 is the full and complete UTF-8 character set and so will show any and every character that can be used in a web address. If there are some obscure characters in the data I suggest you change the column to a BLOB column before then changing it to a utf8mb4 column, because this will preserve the correct character definitions as input rather than as assumed by MySQL on the data already entered.

You do not want utf8_ character sets, in MySQL that is as good as broken, what you want is utf8mb4, the standard UTF8 definition in MySQL is compromised because it saves 4-byte characters in 3-byte blocks and thus corrupts saved character data.