如何修复损坏的 SQL 视图

发布于 2024-12-15 21:43:39 字数 453 浏览 1 评论 0原文

我正在使用开源 CMS pimcore (http://www.pimcore.org)，它运行在MySQL 后端。

它使用相当复杂的视图来表示对象，并且当视图引用的另一个表中的列被重命名时，其中一个视图在某个阶段已损坏。每当我尝试通过 SQL 命令与表交互时，我都会收到错误：

查看“barriste_website.object_6”引用无效表或视图的列或函数或定义者/调用者缺乏使用权他们

想简单地更新视图以引用重命名的列，但在开始研究之前我需要知道视图的当前结构 - 一旦视图被破坏，我该如何检索视图的结构？我已经尝试过

SHOW CREATE VIEW object_6

，但收到同样的错误。

原文

I am using the open source CMS pimcore (http://www.pimcore.org), which runs on a MySQL backend.

It uses fairly complicated views to represent objects and one of them has become broken at some stage when a column in another table being referenced by the view was renamed. Whenever I try to interact with the table via SQL commands I receive the error:

View 'barriste_website.object_6' references invalid table(s) or
column(s) or function(s) or definer/invoker of view lack rights to use
them

I would like to simply update the view to reference the renamed columns, but I need to know the current structure of the view before I start poking around - how exactly do I retrieve the structure of the view once it is broken? I've tried

SHOW CREATE VIEW object_6

but I receive the same error.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

心如狂蝶 2024-12-22 21:43:39

MySQL：

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';

参考：INFORMATION_SCHEMA VIEWS 表

SQL Server：

USE databasename
GO

EXEC sp_helptext viewName

或者也是这样的查询：

SELECT TABLE_NAME as ViewName,
VIEW_DEFINITION as ViewDefinition
FROM INFORMATION_SCHEMA.Views

您可以在其中添加 WHERE 以仅检索一个视图

MySQL:

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';

reference: The INFORMATION_SCHEMA VIEWS Table

SQL Server:

USE databasename
GO

EXEC sp_helptext viewName

or also a query like this:

SELECT TABLE_NAME as ViewName,
VIEW_DEFINITION as ViewDefinition
FROM INFORMATION_SCHEMA.Views

where you could add a WHERE to only retrieve one view

回复收藏 0 原文

云裳 2024-12-22 21:43:39

只需使用“drop view object_6”删除视图，然后进入 pimcore 后端并再次保存该类。然后视图会自动重新生成。

回复收藏 0 原文

篱下浅笙歌 2024-12-22 21:43:39

就我而言，罪魁祸首是使用 ORDER BY 和别名导致了问题。我将我的观点从：更改

CREATE VIEW v_storename_totalnamelength AS
SELECT
  (char_length(`a`.`ExtractedLongName`) + char_length(`a`.`ExtractedLongName`)) AS `TotalNameLength`
FROM
  `promoter`.`v_storename_extracted` `a`
ORDER BY
  `TotalNameLength` DESC
;

为：

CREATE VIEW v_storename_totalnamelength AS
SELECT
  (char_length(`a`.`ExtractedLongName`) + char_length(`a`.`ExtractedLongName`)) AS `TotalNameLength`
FROM
  `promoter`.`v_storename_extracted` `a`
ORDER BY
  (char_length(`a`.`ExtractedLongName`) + char_length(`a`.`ExtractedLongName`)) DESC
;

In my case the culprit was to use ORDER BY with an alias causing the issue. I changed my view from:

CREATE VIEW v_storename_totalnamelength AS
SELECT
  (char_length(`a`.`ExtractedLongName`) + char_length(`a`.`ExtractedLongName`)) AS `TotalNameLength`
FROM
  `promoter`.`v_storename_extracted` `a`
ORDER BY
  `TotalNameLength` DESC
;

to:

CREATE VIEW v_storename_totalnamelength AS
SELECT
  (char_length(`a`.`ExtractedLongName`) + char_length(`a`.`ExtractedLongName`)) AS `TotalNameLength`
FROM
  `promoter`.`v_storename_extracted` `a`
ORDER BY
  (char_length(`a`.`ExtractedLongName`) + char_length(`a`.`ExtractedLongName`)) DESC
;

回复收藏 0 原文

~没有更多了~