MySQL Bugs: #6615: problem with mysqldump and views

Bug #6615	problem with mysqldump and views
Submitted:	14 Nov 2004 20:51	Modified:	15 Nov 2004 20:14
Reporter:	Martin Gunger	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.0.1-alpha-nt	OS:	Windows (Windows 2003 Server)
Assigned to:		CPU Architecture:	Any

Description:
Looks like mysql can create "create view" command but not mysqldump.

The result I get is "mysqldump: Can't get CREATE TABLE for table `images_tree` (Table 'images_tree' was not locked with LOCK TABLES)"

>show tables;
+------------------+------------+
| Tables_in_images | table_type |
+------------------+------------+
| Lots of tables
| images_tree      | VIEW       |
| Lots of tables
+------------------+------------+

So far as I can se, it looks like mysqldump treats the view as a table?

How to repeat:
Just create a view and try to backup the db with mysqldump.

My commandline looks like this:
"c:\Program Files\mysql\bin\mysqldump.exe" --all-databases -u backup -p******** > c:\*****\%DATE%.sql

Hi,

Thank you for the report, but I wasn't able to repeat it with 5.0.2-debug-log.

Hi,
  I can certainly reproduce this problem with Mysql 5.0.9 (mysqldump 10.10) under Windows XP Professional.

Regards

This isn't really a bug that I can tell, more like weird behavior... read on.

Linux klsjf 2.6.12-1.1398_FC4smp #1 SMP Fri Jul 15 01:30:13 EDT 2005 i686 i686 i386 GNU/Linux

-- MySQL dump 10.10
--
-- Host: localhost    Database: iantest
-- ------------------------------------------------------
-- Server version	5.0.9-beta-standard-log

/*!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 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) default NULL,
  `id2` int(11) default NULL,
  `id3` int(11) default NULL,
  `id4` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `test_v`
--

DROP TABLE IF EXISTS `test_v`;
DROP VIEW IF EXISTS `test_v`;
CREATE TABLE `test_v` (
  `id` int(11) default NULL,
  `id2` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- View structure for view `test_v`
--

DROP TABLE IF EXISTS `test_v`;
DROP VIEW IF EXISTS `test_v`;
CREATE ALGORITHM=UNDEFINED VIEW `iantest`.`test_v` AS select `iantest`.`test`.`id` AS `id`,`iantest`.`test`.`id2` AS `id2` from `iantest`.`test`;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

----

This works just fine... the view works correctly (provided there is data in the table).. but what 
confuses me is this:

--
-- Table structure for table `test_v`
--

DROP TABLE IF EXISTS `test_v`;
DROP VIEW IF EXISTS `test_v`;
CREATE TABLE `test_v` (
  `id` int(11) default NULL,
  `id2` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- View structure for view `test_v`
--

DROP TABLE IF EXISTS `test_v`;
DROP VIEW IF EXISTS `test_v`;

What purpose does the above SQL serve? Things work out fine either way but the above has no
point that I can see. Can anyone clarify?

I find this particular issue very annoying. I have seen it ever since views were available, in every beta release of MySQL 5 I've used. I work on Linux systems, not Windows, so I can not say it is exactly the same, but it looks like the same thing.

To use mysqldump with a database containing views, use the options...

-f -l --opt

Those work for me.

Now, if the MySQL crew will simply create a method for dumping view definitions... My primary method of database backups right now is using mysqldump with a cron job. But since views don't get dumped, I have no way to automatically backup the defintions which I can easily see by doing a "SHOW CREATE VIEW". So, I have to remember to go in an type SHOW CREATE VIEW occasionally and dig the SQL code out to make view definition backups. I'm sure there is an easier way already, but why can't mysqldump dump views?

Bleh.

What is the resolution of this bug?

I get exactly the same problem.  Why does mysqldump insist on attempting to create a View as a table.  This is the error message I get when attempting to restore dumped Views:

ERROR 1050 (42S01): Table 'V_Meter_H_Utility' already exists

Can the mysql team please update this bug.

Thanks,

Kevin

Sorry, the versions of MySQL I'm running are as follows:

Fedora Core 3:
mysql  Ver 14.12 Distrib 5.0.15, for pc-linux-gnu (i686) using readline 5.0

and

Redhat Lunix Enterprise 4:
mysql  Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline 5.0

Regards,

Kevin

Kevin, we need more information. What is the dump that caused that error ? How do you call mysqldump - what command-line arguments ?