Bug #28828 View recreated as a table from backup insted of an view...
Submitted: 1 Jun 2007 8:38 Modified: 4 Feb 2009 14:26
Reporter: Johan Jakobsson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Administrator Severity:S1 (Critical)
Version:1.2.12 and comdb v 5.0.41, 1.2.14 OS:Windows
Assigned to: Mike Lischke CPU Architecture:Any

[1 Jun 2007 8:38] Johan Jakobsson
Description:
View recreated as a table from backup insted of an view... The viwes definitions is all gone...
This Bugg existed in version 5.0.33 I discovered the hard way.....
Still exists in db 5.0.41.  There fore this report.

How to repeat:
Make shure your db contains a view.
Using mysql administrator 1.2.12 I create database backup "online with bindingslog" Also tested Inodb back (the same).

I then drops the orgin db or restore it on an other db that dosn't hold the db.
When I'll recreat it The backup hangs and do not finish the final steps. Have to kill it. Everything is ok exept the fact that the view is recreated as at table. And the view definition is gone.... Tok me a day to recreate the views...
Now I'll also take a backup of the Information _schema as a security backup... but you shuldn't need to do this.
[1 Jun 2007 9:15] Sveta Smirnova
Thank you for the report.

But it seems to be MySQl Administrator bug, because you create dump using MySQL Administrator.

I can not repeat described behaviour with Mac version of MySQL Administrator and MySQL 5.0.41. Please indicate your operating system.

Also please connect to the database using command line client or MySQL Query Browser and provide output of SHOW CREATE VIEW problem_view.
[1 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".
[25 Nov 2008 7:01] Iresh Patel
no, he is correct. i am also facing same problem. 
repeat 
Create database with view.
backup database from mysql administrator. remove "add drop statement"
restore database.

there is no view. all view you found as a table.
[25 Nov 2008 7:01] Iresh Patel
no, he is correct. i am also facing same problem. 
repeat 
Create database with view.
backup database from mysql administrator. remove "add drop statement"
restore database.

there is no view. all view you found as a table.
[26 Nov 2008 6:44] Sveta Smirnova
Iresh,

thank you for the feedback.

Please indicate your operating system.

Also please connect to the database using command line client or MySQL Query Browser and
provide output of SHOW CREATE VIEW problem_view.
[26 Nov 2008 6:52] Iresh Patel
Operating System : Xp sp3
Mysql Administrator Version : 1.2.12

'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `viewpurchase` AS select `costingd`.`vrno` AS `vrno`,`goldgrn`.`itemcode` AS `itemcode`,`diastock`.`LOCATION` AS `LOCATION`,`diastock`.`BRCD` AS `BRCD`,`diastock`.`ARTICLE` AS `ARTICLE`,`diastock`.`CARAT` AS `CARAT`,`costingm`.`vrdate` AS `TRANSDATE`,_utf8'PURCHASE' AS `TRANSTYPE`,`goldgrn`.`qty` AS `qty`,`goldgrn`.`grossweight` AS `grossweight`,`diastock`.`CATEGORYCD` AS `CATEGORYCD`,`goldgrn`.`subcategorycd` AS `subcategorycd`,_utf8'IN' AS `TRANSCODE` from (((`costingm` join `costingd` on((`costingm`.`vrno` = `costingd`.`vrno`))) join `goldgrn` on((`costingd`.`bulkcode` = `goldgrn`.`bulkcode`))) join `diastock` on((convert(`goldgrn`.`itemcode` using utf8) = `diastock`.`ITEMCODE`)))'
[26 Nov 2008 7:16] Sveta Smirnova
Thank you for the feedback.

Please try with current version of MySQL Administrator 1.2.14 and if problem still exists provide output of SHOW CREATE TABLE for all underlying tables.
[26 Nov 2008 8:04] Iresh Patel
Still same problem with 1.2.14
This is View

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vwdocexpiry` AS select `mstempl`.`empnm` AS `empnm`,_utf8'PP EXPIRY' AS `DOCGROUP`,`mstempl`.`pp_expiry` AS `DATEOFEXPIRY` from `mstempl` where (isnull(`mstempl`.`leftdate`) or (left(`mstempl`.`empcd`,1) = _latin1'X')) union all select `mstempl`.`empnm` AS `empnm`,_utf8'RP EXPIRY' AS `DOCGROUP`,`mstempl`.`rp_expiry` AS `DATEOFEXPIRY` from `mstempl` where (isnull(`mstempl`.`leftdate`) or (left(`mstempl`.`empcd`,1) = _latin1'X')) union all select `mstempl`.`empnm` AS `empnm`,_utf8'LICENSE EXPIRY' AS `DOCGROUP`,`mstempl`.`license_expiry` AS `DATEOFEXPIRY` from `mstempl` where ((`mstempl`.`license_holder` = 1) and (isnull(`mstempl`.`leftdate`) or (left(`mstempl`.`empcd`,1) = _latin1'X'))) union all select `mstempl`.`empnm` AS `empnm`,_utf8'DDF GATEPASS EXPIRY' AS `DOCGROUP`,`mstempl`.`ddfgatepassexpiry` AS `DATEOFEXPIRY` from `mstempl` where ((trim(ifnull(`mstempl`.`ddfgatepass`,_utf8'')) <> _utf8'') and (isnull(`mstempl`.`leftdate`) or (`mstempl`.`leftdate` = _utf8'') or (left(`mstempl`.`empcd`,1) = _latin1'X'))) union all select `docexpiry`.`docName` AS `DOCNAME`,`docexpiry`.`docGroup` AS `DOCGROUP`,`docexpiry`.`dateofExpiry` AS `DATEOFEXPIRY` from `docexpiry` order by `DOCGROUP`,`DATEOFEXPIRY`

This is tables
MSTEMPL

CREATE TABLE `mstempl` (
  `empcd` char(4) character set latin1 NOT NULL default '',
  `empnm` varchar(45) NOT NULL,
  `ppno` varchar(15) NOT NULL,
  `pp_expiry` date NOT NULL default '0000-00-00',
  `nationality` varchar(20) default NULL,
  `rpno` varchar(15) default NULL,
  `rp_expiry` date default NULL,
  `address` varchar(100) default NULL,
  `country` varchar(15) default NULL,
  `phoneno` varchar(25) default NULL,
  `mobileno` varchar(25) default NULL,
  `joindate` date NOT NULL default '0000-00-00',
  `basic` decimal(8,2) default '0.00',
  `hra` decimal(8,2) default '0.00',
  `allowance` decimal(8,2) default '0.00',
  `totsalary` decimal(8,2) default '0.00',
  `license_holder` bit(1) default NULL,
  `license_expiry` date default NULL,
  `bloodgroup` varchar(3) default NULL,
  `leftdate` date default NULL,
  `city` varchar(25) default NULL,
  `bankcode` varchar(5) default NULL,
  `bankacno` varchar(25) default NULL,
  `designation` varchar(25) default NULL,
  `brcd` char(1) default NULL,
  `banktran` char(1) default NULL,
  `pp_issuedate` date default NULL,
  `pp_issueplace` varchar(25) default NULL,
  `visatype` char(1) default NULL,
  `ddfgatepass` varchar(1) default NULL,
  `ddfgatepassexpiry` date default NULL,
  `birthdate` date default NULL,
  `emailid` varchar(50) default NULL,
  PRIMARY KEY  (`empcd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `docexpiry` (
  `docCode` int(10) unsigned NOT NULL auto_increment,
  `docName` varchar(60) NOT NULL,
  `dateofExpiry` date NOT NULL default '0000-00-00',
  `docDetail` varchar(100) default NULL,
  `docGroup` varchar(30) NOT NULL default '',
  `EndDate` date default NULL,
  PRIMARY KEY  (`docCode`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8
[26 Nov 2008 8:34] Sveta Smirnova
Thank you for the feedback.

Error is not repeatable on Mac for me. So I re-open the report for my colleague could test it on Windows.
[1 Dec 2008 9:09] Iresh Patel
No any further response found yet.
[1 Jan 2009 20:23] MySQL Verification Team
Verified on Vista 64-bit with instructions commented at:

[25 Nov 2008 8:01] Iresh Patel

below error displayed when trying to restore (the view as create table statement)

Error while executing this query:CREATE TABLE `vwdocexpiry` (
  `empnm` varchar(60),
  `DOCGROUP` varchar(30),
  `DATEOFEXPIRY` date
) DEFAULT CHARSET=utf8;
The server has returned this error message:Table 'vwdocexpiry' already exists
MySQL Error.
[21 Jan 2009 12:35] Pavel Nunez
Scenario: 

OS: 
Linux: openSUSE 11.0 - Kernel: 2.6.25.18
MySQL: 

Server: 5.0.51a
mysqldump: 10.11
MySQL Query Browser: 1.2.12
MySQL Administrator: 1.2.12
MySQL Administrator dump: 1.4

The latest version available on my repositories. 

The file I've posted has three files on it: my_sample_view.sql, db_mysqldump10.sql and db_MySQLAdministratorDump1_4.sql

1 - Note that 'db_mysqldump10.sql' will end with a database that misses the view. In other words it never recreates that view again, because the commands for doing so are commented. 

2 - Note that 'db_MySQLAdministratorDump1_4.sql' will restore a database that will have two files: the original view plus a new table that has the same name and structure of the view. This is not desirable since I don't want a new table for every single view. 

For creating the files y used the following: 

File 1: 
# mysqldump -u root -p database > /home/user/Desktop/dp_mysqldump10.sql

File 2:
MySQL Administrator backup utility: /home/user/Desktop/dp_MySQLAdministrator10.sql

File 3:
The 'Copy SQL to Clipboard' option over the view from MySQL Query Browser opened an editor (Kwrite) pasted the contents on it and saved the file as: /home/user/Desktop/my_sample_view.sql

If you look at the files you will notice that there are some incongruencies that need to be check. 

Thanks
[4 Feb 2009 14:26] Mike Lischke
Fixed in repository administrator, revision 395.