MySQL Forums
Forum List  »  Views

Problem Migrate VIEW from MySQL 5.5 to 5.7
Posted by: Lucas carraro
Date: June 08, 2017 04:51AM

In a server with MySQL 5.5 i have a specific situation:


Server version: 5.5.54 MySQL Community Server (GPL)


> SHOW FULL TABLES IN db WHERE TABLE_TYPE LIKE 'VIEW';
+---------------------+------------+
| Tables_in_proadess2 | Table_type |
+---------------------+------------+
................
| values_gr2 | VIEW |
................



> describe values_gr2;

.................................

| Z13300150000000000000 | float(11,1) | YES | | NULL | |
| Z13300150000000000001 | float(11,1) | YES | | NULL | |
| Z13300150000000000002 | float(11,1) | YES | | NULL | |
| Z13300150000000000003 | float(11,1) | YES | | NULL | |
| Z13300150000000000004 | float(11,1) | YES | | NULL | |
+-----------------------+-------------+------+-----+---------+-------+
4468 rows in set (0.14 sec)


PS: Unfortunately I do not have access to the application code that uses this database. I know that this number of columns is absurd and would probably solve with better data modeling. But this is not a choice for me. it's a legacy application.


I have problems with dump of this database in 5.5.

mysqldump db > db.sql

-- Warning: Creating a stand-in table for view values_gr2 may fail when replaying the dump file produced because of the number of columns exceeding 1000. Exercise caution when replaying the produced dump file.

But this view is used only in read only operations ( select )

If i need to restore i can run the code that generate this view.
Dump and restore tables and run the SQL to create this view.


I try to migrate this VIEW to mysql 5.7 but receive this error

mysql -u root -p db < create-views- values_gr2.sql
ERROR 1117 (HY000) at line 1: Too many columns


The code of the view is something like this.


create VIEW values_gr2 AS
select
.......
values_gr11.A02000030000000000000,
values_gr11.A02000030010800000000,
......
values_gr21.A02000030091100000000,
.
.
.
.
.
from
values_br11, values_br12, values_br21, values_br22
where
values_br11.cod = values_br12.cod and
values_br12.cod = values_br21.cod and
values_gr21.cod = values_gr22.cod
;



Anyone has a suggestion ? its possible to run this view in mysql 5.7 ? Any workaround ?

Regards

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem Migrate VIEW from MySQL 5.5 to 5.7
2054
June 08, 2017 04:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.