#1265 - Data truncated for column MYSQL 5.7

Question

After i upgraded my Server to PHP7 and MySQL 5.7 i get an Error with this Query:

UPDATE user SET user_rank_points = (@i := (@i + 1))

The Error is: #1265 - Data truncated for column 'user_rank_points' at row 1

Has anyone a solution?

Answer 1

These kinds of questions are going to come up a lot as people upgrade to MySQL 5.7. This release of MySQL made the strict SQL mode the default, unlike earlier versions where strict mode was an option.

Without strict mode, when you try to store a value like 32768 into a SMALLINT, it would silently truncate the value to 32767, the largest value you can store in a signed 16-bit integer.

Do you prefer that MySQL change the data you try to store? If so, you can change the sql_mode to remove the strict mode, and all values over 2¹⁵-1 will be truncated to that value. Read about how to do that here: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Or do you prefer that it store larger integer values? If so, change your column to an INT.

Or do you prefer that it raises an error when your @i goes over 2¹⁵-1? If so, this is the behavior it has now.

---

Re your comment:

First of all, INT(11) doesn't mean anything different from INT(2) or INT(327). The number argument is only a hint for display width, it has nothing to do with the number of digits the INT will store. See my answer to Types in MySQL: BigInt(20) vs Int(20)

You say you are still getting the same error after changing the column type to INT. What is the value of @i? Run this query after you get the error:

SELECT @i;

Answer 2

I think the Problem is the @i is not initialized in this code:

if(!$db->query('SET @i:=0')) {
    $sdl->log('- Error: Some Error OutputCONTINUE');
}
else {
    $sql = 'UPDATE user
            SET user_rank_points = (@i := (@i + 1))
            WHERE user_active=1 ORDER BY user_points DESC';

    if(!$db->query($sql)) {
        $sdl->log('- Error: Some Error Output');
    }
}

Because if i enter the two lines in the SQL Editor in PHPMYADMIN the Code is executed without Problems

Answer 3

I found a solution with using mysqli_multi_query() instead of mysqli_query()

$sql='SET @i;';
$sql.='SET user_rank_points = (@i := (@i + 1))
      WHERE user_active=1 ORDER BY user_points DESC';

$link = mysqli_connect("localhost", "my_user", "my_password", "world");
mysqli_multi_query($link,$sql);

I don't know if this a safe solution but it did my job.