CODEWITHSUNDEEP
mysqlmysql-workbench
Andrew Bellenie
Andrew Bellenie

Reputation: 47

ALTER VIEW inserts database prefix for one view only

Having a strange problem.

I use several db views in my app. All of the views just reference their tables without prefix the db name, e.g.

SELECT  `foo`, `bar`
FROM    `tablename`

However, I have one view which is as follows:

SELECT  `foo`, `bar`
FROM    `database_name`.`table_name`

This is causing headaches when performing schema comparisons.

I've edited the view several times using ALTER VIEW but every time it reinserts the database name as a prefix. I've also dropped and re-created it from scratch but same result.

It only does it for this one view though.

I'm using MySQL Workbench 6.3.10. Here's the full statement:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `reporting_base` AS
    SELECT 
        `b`.`creditor_id` AS `creditor_id`,
        `c`.`creditor_name` AS `creditor_name`,
        `c`.`group_name` AS `group_name`,
        `rd`.`base_date` AS `base_date`,
        `rd`.`year` AS `year`,
        `rd`.`period` AS `period`,
        `rd`.`week` AS `week`,
        `b`.`id` AS `branch_id`,
        `b`.`branch_name` AS `branch_name`,
        `b`.`area` AS `area`,
        `b`.`manager` AS `manager`,
        `s`.`id` AS `staff_id`,
        `s`.`type` AS `staff_type`,
        `s`.`full_name` AS `staff_name`
    FROM
        (((`database_name`.`reporting_dates` `rd`
        JOIN `database_name`.`branches` `b`)
        JOIN `database_name`.`creditors` `c` ON (((`c`.`id` = `b`.`creditor_id`)
            AND (`c`.`include_in_reports` = 1))))
        LEFT JOIN (SELECT 
            0 AS `id`,
                `database_name`.`creditors`.`id` AS `creditor_id`,
                'n/a' AS `type`,
                'n/a' AS `full_name`
        FROM
            `database_name`.`creditors` UNION SELECT 
            `database_name`.`staff`.`id` AS `id`,
                `database_name`.`staff`.`creditor_id` AS `creditor_id`,
                `database_name`.`staff`.`type` AS `type`,
                `database_name`.`staff`.`full_name` AS `full_name`
        FROM
            `database_name`.`staff`) `s` ON ((`s`.`creditor_id` = `c`.`id`)))
    WHERE
        (`rd`.`base_date` <= UTC_DATE())

Upvotes: 1

Views: 240

Answers (1)

Natarajan N Napoleon
Natarajan N Napoleon

Reputation: 367

If there is ambiguity in the whole create view statement...it may add database name.

...from (((''table1' 'rd' 
join 'dbname'.'table2' 'b'...

itself is enough to trigger 'dbname' into the view code.

Upvotes: 1

Related Questions