Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7406

I can't get from INFORMATION_SCHEMA original SQL code creating VIEW

    XMLWordPrintable

    Details

      Description

      I am use INFORMATION_SCHEMA for speedup getting database definition

      And I found yet another problem:

      Demonstration:

      CREATE TABLE `test` (
        `a` MEDIUMTEXT,
        `b` MEDIUMTEXT
      ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
       
      CREATE VIEW `view_test` AS
      (SELECT * FROM test);
       
      SELECT  `TABLE_NAME`
      	,`TABLE_NAME`
      	,`VIEW_DEFINITION`
      FROM `information_schema`.`VIEWS` IS_V
      	WHERE IS_V.`TABLE_SCHEMA`='test' AND IS_V.table_name = 'view_test'

      TABLE_NAME  TABLE_NAME  VIEW_DEFINITION                                                                
      ----------  ----------  -------------------------------------------------------------------------------
      view_test   view_test   (SELECT `test`.`test`.`a` AS `a`,`test`.`test`.`b` AS `b` FROM `test`.`test`)

      SHOW CREATE VIEW `view_test`

      VIEW       CREATE VIEW                                                                                                                                              character_set_client  collation_connection  
      ---------  -------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------  ----------------------
      view_test  CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS (SELECT `test`.`a` AS `a`,`test`.`b` AS `b` FROM `test`)  utf8                  utf8_general_ci    

      As you can see when I get SQL definition with "SHOW CREATE VIEW" I get body without database `test` as expected. When I get definition from INFORMATION SCHEMA I have definition with database `test`. If I will use this SQL code for create view in different database I would have problems.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              mikhail Mikhail Gavrilov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.