Bug #18462 mysqldump does not dump view structures correctly
Submitted: 23 Mar 2006 16:02 Modified: 4 Jul 2006 9:09
Reporter: bocquet denis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S2 (Serious)
Version:5.0.22-BK, 5.0.19 OS:Linux (linux 2.6.12)
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any

[23 Mar 2006 16:02] bocquet denis
Description:
the create command for the view is incomplete

DROP TABLE IF EXISTS `v`;
/*!50001 DROP VIEW IF EXISTS `v`*/;
/*!50001 DROP TABLE IF EXISTS `v`*/;
/*!50001 CREATE TABLE `v` (
  `qty` int(11),
  `price` int(11),
  `value` bigint(21)
) */;
`.`qty` AS `qty`,`t`.`price` AS `price`,(`t`.`qty` * `t`.`price`) AS `value` from `t` */;

How to repeat:
create database test;
use test;
CREATE TABLE t (qty INT, price INT);
INSERT INTO t VALUES(3, 50);
INSERT INTO t VALUES(5, 51);
CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysqldump \
          -hlamachine -Pleport -uxy -plebon --master-data=2 -F -R \
          --single-transact --hex-blob \
          --max_allowed_packet 4096 \
          --fields-terminated-by="ZZYYXX", \
          --lines-terminated-by="\n" \
          --tab .\
 test -r test_7.sql
[27 Mar 2006 8:48] bocquet denis
more information : when you make a backup with mysqldump of your database with one file per table, the view created in the database are not saved correctly. You find in the file only the part of the end of the line with comment but the start comment and the start of line isn't writing.
[27 Apr 2006 14:25] Valeriy Kravchuk
Sorry for a long delay with this bug report. Verified with your test case on 5.0.22-BK on Linux. This way it works (I put ... instead of non-relevant output):

openxs@suse:~/dbs/5.0> bin/mysqldump -uroot -F dbv
-- MySQL dump 10.10
--
-- Host: localhost    Database: dbv
-- ------------------------------------------------------
-- Server version       5.0.22
...
--
-- Table structure for table `v`
--

DROP TABLE IF EXISTS `v`;
/*!50001 DROP VIEW IF EXISTS `v`*/;
/*!50001 DROP TABLE IF EXISTS `v`*/;
/*!50001 CREATE TABLE `v` (
  `qty` int(11),
  `price` int(11),
  `value` bigint(21)
) */;

--
-- View structure for view `v`
--

/*!50001 DROP TABLE IF EXISTS `v`*/;
/*!50001 DROP VIEW IF EXISTS `v`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v` AS select `t`.`qty` AS `qty`,`t`.`price` AS `price`,(`t`.`qty`
 * `t`.`price`) AS `value` from `t` */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
...

But when I added --tab=., I've got:

openxs@suse:~/dbs/5.0> bin/mysqldump -uroot -F --tab=. dbv
openxs@suse:~/dbs/5.0> cat v.sql
-- MySQL dump 10.10
--
-- Host: localhost    Database: dbv
-- ------------------------------------------------------
-- Server version       5.0.22

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `v`
--

DROP TABLE IF EXISTS `v`;
/*!50001 DROP VIEW IF EXISTS `v`*/;
/*!50001 DROP TABLE IF EXISTS `v`*/;
/*!50001 CREATE TABLE `v` (
  `qty` int(11),
  `price` int(11),
  `value` bigint(21)
) */;
 select `t`.`qty` AS `qty`,`t`.`price` AS `price`,(`t`.`qty` * `t`.`price`) AS `value` from `t` */;
...

The result is different, but that "*/"  without a matching "/*" is surely a bug!
[31 May 2006 11:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7084
[6 Jun 2006 21:20] Iggy Galarza
This looks good.
[26 Jun 2006 18:16] Tatiana Azundris Nuernberg
fixed in 5.0.23
[4 Jul 2006 9:09] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html
[4 Jul 2006 9:10] Jon Stephens
Documented bugfix in 5.0.23 changelog. CLosed.