0

(update since this question was marked as duplicate) I am not asking about error 1406 itself. I know what that means. In fact if you read the following, you will see that I specifically want an error like that. I am also not directly asking about strict mode in MySQL. I am aware that it exists and it is active (as proven by the following example from mysql CLI). The third "duplicate" is somewhat useful, but also does not really answer the question. It only mentions strict mode in regard to security. It was somewhat useful in bringing me on the right track, but it is simply not a duplicate.

Please reopen this question. (/update)

I have a project with a very simple model. It has only a few fields, one of which is a comment, stored as varchar(255).

If I were storing more than 255 characters via mysql command line tool, I'd get a 

ERROR 1406 (22001): Data too long for column 'comment' at row 1
MariaDB [testing]> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Error | 1406 | Data too long for column 'comment' at row 1 |
+-------+------+---------------------------------------------+

This is good. I want this error in Laravel! But when I do the same via my Laravel Model, it will happily tell me it would have been stored. Essentially, I can do this:

$longPayload = str_repeat("a", 1000);
$suspiciousEntry = MyModel::fromInput(array('comment' => $longPayload));
$suspiciousEntry->save(); // this will return true!

But even though this has returned true the 1000 characters have not been stored in the database. Of course only the first 255 characters end up there.

My question is: how would I know about this at run time? I would have loved to see the mysql-error 1406 or an exception somewhere. But at some point laravel must have chosen to truncate the data (or mysql would have rejected it).

In such cases, I would prefer to be able to throw some kind of warning back at the user, something like a HTTP 422 Unprocessable Entity. Right now, there is a risk of data loss, which I'm, uncomfortable with.

(Note: This is not a question on how to make the field larger. I have since converted it to be a MEDIUMTEXT. But if that ever were maxed out, I would also never know and lose data.)

amenthes
  • 3,117
  • 1
  • 32
  • 38
  • You can turn strict mode on in MySQL. See https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_strict_all_tables – ceejayoz Aug 12 '17 at 18:43
  • Then, let this question be named "how to enable strict mode in Laravel". Whoever flagged this as duplicate did not read it fully. Two of the three "answered" things only tell me what I have already given in my question (that this is truncated) and the last one does not answer how to achieve strict mode in Laravel. – amenthes Aug 12 '17 at 21:08
  • Since I can't even answer it myself, now: the MySQL section has a "strict" flag in `config/database.php`, with this set to `true` - which is apparently not the default, one gets a `PDOException` instead of silent data loss. – amenthes Aug 12 '17 at 21:17
  • If anyone has a laravel project that has been started before May 2016, there's a high chance you run into the same. Strict mode has been introduced as default on 2016-05-10 (see: https://github.com/laravel/laravel/commit/f237656c687bec2c53a09c9eefbe9c897aacdf1c ) – amenthes Aug 13 '17 at 09:13

1 Answers1

1

I have since found the answer: MySQL / MariaDB knows a strict mode in more recent versions. The command line interface seems to be using this.

Laravel introduced a setting for this somewhere around Laravel 5, but initially that defaulted to false. If you created your project before Laravel 5.3 (Commit from May 2016), your config/database.php will probably contain this setting set to false.

As long as this is set to false, you may lose data at any point and never know about it.

If you set it to true, you will now receive a \PDOException - and this can be handled as you see fit. In case you don't handle it, you'll probably receive a HTTP 500 error - which in my book is still better than silent data loss.

in short: Set config/database.php up to use 'strict' => true for your mysql adapter, if you haven't already done so.

amenthes
  • 3,117
  • 1
  • 32
  • 38