0

I am running a count(*) on a view, but I get the following error. The view has several joins and views. But this error should only apply when changing a value on a table field, right?

mysql> select count(*) from dm.vSscore24 ;                                             

ERROR 1406 (22001): Data too long for column 'pbs' at row 1
mysql>

db info
mysql> show variables like '%version%';
+-------------------------+-----------------------------+
| Variable_name           | Value                       |
+-------------------------+-----------------------------+
| innodb_version          | 5.7.20                      |
| protocol_version        | 10                          |
| slave_type_conversions  |                             |
| tls_version             | TLSv1,TLSv1.1               |
| version                 | 5.7.20-0ubuntu0.16.04.1-log |
| version_comment         | (Ubuntu)                    |
| version_compile_machine | x86_64                      |
| version_compile_os      | Linux                       |
+-------------------------+-----------------------------+

thank you.

Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
João Pinela
  • 107
  • 1
  • 8
  • Does the view(s) contain any user defined variables? – P.Salmon Jan 05 '18 at 12:36
  • hi @P.Salmon . no, but it did call some functions, with small variable sizes. thank you for your comment – João Pinela Jan 05 '18 at 12:58
  • @P.Salmon views can't contain MySQL user variables. `CREATE VIEW test AS SELECT @test CROSS JOIN (SELECT @test := 'test') AS init_user_params;` wil generate a SQL error `Error Code: 1351 View's SELECT contains a variable or parameter` – Raymond Nijland Jan 05 '18 at 13:18

1 Answers1

0

found the issue.

It was a function called inside a view that had a variable (named pbshape) too small for the maximum size of the column.

Luckily there were no columns named 'pbs' anywhere (in tables or views), and that made me wonder. If the variable were named like bs I think I would never have found the answer :P

The word column in the error message 'Data too long for column' is extremely missleading.

thank you guys

João Pinela
  • 107
  • 1
  • 8
  • Nice next time also post de code of the view and the function then we can help you better... Well the variable @pbshape will be rendered as a column within MySQL engine so the error `Data too long for column` isn't really missleading. – Raymond Nijland Jan 05 '18 at 13:21