I won't go into why I did this, but I created views built on other views in a MySQL database. When we dump and re-import the database using:
mysql -u user -p firstDB > [path]\myDatabase.sql
mysql -u user -p secondDB < [path]\myDatabase.sql
it throws the error
ERROR 1439 (42000) at line 8889: Display width out of range for column 'iteration_number' (max = 255)When the MySQL database is dumped, it is creating tables for the views that must be used as the source for the other views. For one of these views, the dump is creating a table and specifying BIGINT(65535) for the iteration_number field. When you re-import this dump file, the import fails with the message above.
I have a number of views built on other views and it seems like the only ones giving me this issue are the ones that I have specified a condition to check if the field is a number before casting to an Integer
if((`r`.`value` REGEXP '[0-9]'), cast(`r`.`value` AS SIGNED), 0) AS `iteration_number`Instead of blindly casting the field to an Integer, like so:
cast(reverse(`r`.`value`) AS SIGNED) AS `iteration_number`MySQL must attempt to dynamically create the field size from the data in the view and when it cannot, such as when there is a condition in the view, it specifies BIGINT(65535). A bug report was already filed with MySQL with a similar problem.
