To support analytics we have created a new 'spotfire' schema we are filling with views on the tables in our main 'de_ie_cm' schema.
As developers we use a db user that has full rights to both schemas.
When we list the data of a view in the 'spotfire' schema with HeidiSQL after fetching the data HeidiSQL is trying to fetch meta data (probably the record count) from the wrong schema which causes an SQL error; see below:
SELECT CURRENT_USER();
SELECT `nc12` FROM `spotfire`.`rcp_critical_part` LIMIT 1000;
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='de_ie_cm';
SHOW TABLE STATUS FROM `de_ie_cm`;
SHOW FUNCTION STATUS WHERE `Db`='de_ie_cm';
SHOW PROCEDURE STATUS WHERE `Db`='de_ie_cm';
SHOW TRIGGERS FROM `de_ie_cm`;
SHOW EVENTS FROM `de_ie_cm`;
SHOW CREATE TABLE `de_ie_cm`.`rcp_critical_part`;
/* SQL Error (1146): Table 'de_ie_cm.rcp_critical_part' doesn't exist */
/* Data in this table will be read-only. */
The connection used specifies using the 'spotfire' schema but it tries to get data about the view from the 'de_ie_cm' schema instead.
Any idea what is causing this and how we can prevent this happening?
I am using HeidiSQL 8.0.0.4396 on Windows 7 but the same happens on HeidiSQL 7.