MySQL Bugs: #28828: View recreated as a table from backup insted of an view...

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

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.

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.

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".

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.

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.

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.

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`)))'

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.

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

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.

No any further response found yet.

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.

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

Fixed in repository administrator, revision 395.