Problem With Case In View

[edmundek]

hello,

i have a “small” problem with exporting DB.

When i export DB as sql dump it lowercas all table name in view definition. Other sturcutres (table name, stored procedure etc.,) are ok.

Iam using 5.1.41 on Win7-64 (lower_case_table_names=2), SqlYog 8.5 Ultimate ed.

The same problem have when try alter view.

Better show problem in expample:

if I create this view

Code:

DELIMITER $$
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `g_view_treeobject` AS (
SELECT
`t`.`treeID` AS `treeID`,
`t`.`internalName` AS `internalName`,
FROM ((`g_Tree` `t`
JOIN `g_TreeAlias` `ta` ON ((`ta`.`treeID` = `t`.`treeID`))
)) $$

DELIMITER ;

and when i try to edit view or export its generate this: (lowercase tablename in sql)

Code:

DELIMITER $$
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `g_view_treeobject` AS (
SELECT
`t`.`treeID` AS `treeID`,
`t`.`internalName` AS `internalName`,
FROM ((`g_tree` `t`
JOIN `g_treealias` `ta` ON ((`ta`.`treeID` = `t`.`treeID`))
)) $$

DELIMITER ;

tx for help…

and hope you understand my english 🙂

[peterlaursen]

1) Just try to execute 'SHOW CREATE VIEW viewname' after creating.  Will it return the same lettercases as the original CREATE statement (or the same as what SQLyog does).  If not this is a server problem.  It should then be reported to bugs.mysql.com if it is same with latest server (5.1.50). I can do that or you can once we have all detail and have verified on 5.1.50

2) If we shall verify it on our environments I need CREATE TABLE statements for the tables as well.

3) Tell on which platform your server is running.

4) Tell what “SHOW GLOBAL VARIABLES LIKE 'lower_case_table_names'” return. Refer: http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/identifier-case-sensitivity.html.  (but at the moment I am actually not sure (I simply forgot!) if this variable is supposed to have effect on VIEWs and if VIEWS are ever case-sensitive.  But I think so. I do remember some bugs in this respect – and even think I posted 1 or 2 bugs reprots about this 2-3 years back). 

It is the `g_TreeAlias` that *lowercases* in the JOIN clause.  Anything else I miss here? 

[peterlaursen]

I just posted this bug report: http://bugs.mysql.com/bug.php?id=56751

To me it looks like they (again) messed up with VIEWs. But It looks to me like your server is a Linux server with setting 'lower_case_table_names' = 0 and then another report should be filed for this.

[peterlaursen]

oops .. I overlooked that you told “5.1.41 on Win7-64 (lower_case_table_names=2)”

[peterlaursen]

I also reported this: http://bugs.mysql.com/bug.php?id=56757

It would be very nice if you could create an account at mysql.com (if you don't have already) and post additional information – like CREATE TABLE for the tables involved.  Also check the CREATE VIEW statement again,please – as I cannot create this VIEW (I get 'syntax error').

[edmundek]

'peterlaursen' wrote on '13:

… Also check the CREATE VIEW statement again,please – as I cannot create this VIEW (I get 'syntax error')…

hello, it was just part of view only for example..

here is full sql for “demonstration”:

tables:

Code:

CREATE TABLE `g_Tree` (
`treeID` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`treeID`)
);
CREATE TABLE `g_TreeAlias` (
`treeID` INT(11) NOT NULL,
`alias` VARCHAR(50) NOT NULL,
PRIMARY KEY (`treeID`,`alias`)
);

view:

Code:

CREATE
VIEW `test`.`g_view_test`
AS
(
SELECT ta.* FROM g_Tree t
INNER JOIN g_TreeAlias ta ON t.treeID=ta.treeID
);

result for “SHOW CREATE VIEW g_view_test”:

Code:

g_view_test CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `g_view_test` AS (
select `ta`.`treeID` AS `treeID`,`ta`.`alias` AS `alias`
from (`g_tree` `t`
join `g_treealias` `ta` on((`t`.`treeID` = `ta`.`treeID`))
)
)
utf8
utf8_general_ci

I will write the same on mysql forum …

Edit: and thank you for your realy quick answer 🙂

[Author]

Posts