Bug #29262 Bug in mysqldump with create view and function
Submitted: 21 Jun 2007 9:02 Modified: 25 Jul 2007 9:54
Reporter: Phil Reid Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:5.0.41 OS:Any
Assigned to: CPU Architecture:Any
Tags: functions, mysqldump, Views

[21 Jun 2007 9:02] Phil Reid
Description:
I've just upgraded to version 5.0.41 in the hope a bug I run into on 5.0.37 had been fixed. I found a reference to it which prompted me to upgrade, but have since lost the orginal reference.

Anyway I have a view that includes a function call like so:
CREATE VIEW `Parts` AS select `Category`.`Category` AS `Category`,`get_real_value`(`Parts`.`Value`) AS `Real_Value` from `Parts` ORDER BY `get_real_value`(`Parts`.`Value`);

Now when I dump this with 5.0.37 I got:
CREATE VIEW `Parts` AS select `Category`.`Category` AS `Category`,`dbname`.get_real_value(`Parts`.`Value`) AS `Real_Value` from `Parts` ORDER BY `dbname`.`get_real_value`(`Parts`.`Value`);

An error was reported on pulling the data back in.

Now with 5.0.41 I get the following output:
CREATE VIEW `Parts` AS select `Category`.`Category` AS `Category`,(`Parts`.`Value`) AS `Real_Value` from `Parts` ORDER BY (`Parts`.`Value`);

Seems things went to far in the fix.

How to repeat:
Create a view that includes a function
mysqldump it
try and import
[21 Jun 2007 9:58] Sveta Smirnova
Thank you for the report.

I can not repeat described behaviour in  my environment.

Please provide output of SHOW CREATE TABLE Parts and SHOW CREATE FUNCTION get_real_value
[22 Jun 2007 7:36] Sveta Smirnova
Thank you for the feedback.

Please also provide output of SHOW CREATE TABLE category
[22 Jun 2007 7:46] Sveta Smirnova
Please also indicate accurate version of your operating system and exact version of MySQL package you use.
[25 Jun 2007 1:11] Phil Reid
show create table category;
CREATE TABLE `category` (
  `pk_Category` int(11) NOT NULL auto_increment,
  `Category` varchar(50) default NULL,
  `Footprint_Filter` varchar(255) default NULL,
  `ts_Updated` varchar(50) default NULL,
  `ts_Created` varchar(50) default NULL,
  PRIMARY KEY  (`pk_Category`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Versions:
mysql  Ver 14.12 Distrib 5.0.41, for Win32 (ia32)
running on WinXP SP2

mysql  Ver 14.12 Distrib 5.0.41, for redhat-linux-gnu (i686) using readline 5.0
runing on RedHat Fedora Core 6, 2.6.19-1.2911.6.5.fc6

Build is from remi @ http://remi.collet.free.fr/rpms/fc6.i386

Regards
Phil
[25 Jun 2007 9:54] Sveta Smirnova
Thank you for the feedback.

I still can not repeat described behaviour with our builds.

Could you please try with our builds accessible from http://dev.mysql.com/downloads/mysql/5.0.html and if you can repeat provide output of SHOW CREATE TABLE pcb_footprints
[25 Jul 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".