| Bug #16440 | views are not dumped correclty with certain mysqldump options | ||
|---|---|---|---|
| Submitted: | 12 Jan 2006 9:17 | Modified: | 7 Aug 2006 15:37 |
| Reporter: | Wolfgang Fahl | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0.18 to 5.0.22 | OS: | Windows (Windows XP) |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[12 Jan 2006 9:17]
Wolfgang Fahl
[12 Jan 2006 10:14]
Valeriy Kravchuk
Thank you for a problem report. Verified just as described. The real problem is the following. According to the manual (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html): "--no-create-info, -t Do not write CREATE TABLE statements that re-create each dumped table." But when this option is icnluded for database with views, each views is dumped as follows: -- -- Dumping data for table `testview` -- /*!40000 ALTER TABLE `testview` DISABLE KEYS */; LOCK TABLES `testview` WRITE; UNLOCK TABLES; /*!40000 ALTER TABLE `testview` ENABLE KEYS */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; While without this option we get: -- -- Table structure for table `testview` -- DROP TABLE IF EXISTS `testview`; /*!50001 DROP VIEW IF EXISTS `testview`*/; /*!50001 DROP TABLE IF EXISTS `testview`*/; /*!50001 CREATE TABLE `testview` ( `oid` varchar(32) ) */; -- -- View structure for view `testview` -- /*!50001 DROP TABLE IF EXISTS `testview`*/; /*!50001 DROP VIEW IF EXISTS `testview`*/; /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testview` AS select `testtable`.`oid` AS `oid` from `testtable`*/; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; That is, some additional (and, may be, not so correct) statements, but correct result. So, with any mysqldump options, views should NOT be treated like tables. This is a bug.
[15 Jan 2006 15:08]
Wolfgang Fahl
when leaving out the --no-create-info a view is handled like a table: DROP TABLE IF EXISTS `wc01_trainingsentryview`; CREATE TABLE `wc01_trainingsentryview` ( `oid` varchar(32) default NULL, So after a backup/restore with mysqldump/mysql the database will be broken and contain TABLES where VIEWS should be. Unfortunately there seems bo be no work around for this so I don't see why this bug is considered "non-critical". The database is not useable for me at this time. Please promote to "critical"
[22 Jun 2006 13:01]
Georgi Kodinov
Thank you for taking the time to report a problem. Unfortunately you are not using a current version of the product your reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/ If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open". Again, thank you for your continued support of MySQL. Additional info: I tried it using server version 5.0.23 and mysqldump from 5.0.23 and 4.1.20. Using no additional options aside from the connection related ones I get : 4.1 mysqldump: -- -- Table structure for table `testview` -- DROP TABLE IF EXISTS `testview`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testview` AS select `testtable`.`oid` AS `oid` from `testtable`; 5.0 mysqldymp: -- -- Temporary table structure for view `testview` -- DROP TABLE IF EXISTS `testview`; /*!50001 DROP VIEW IF EXISTS `testview`*/; /*!50001 CREATE TABLE `testview` ( `oid` varchar(32) ) */; -- -- Final view structure for view `testview` -- /*!50001 DROP TABLE IF EXISTS `testview`*/; /*!50001 DROP VIEW IF EXISTS `testview`*/; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `testview` AS select `testtable`.`oid` AS `oid` from `testtable` */; If I add to the above "--no-create-info" in both 4.1 and 5.0 I get no mention of the view whatsoever. Note that wihtout --no-create-info in 5.0 there is an additional trick involved : Create a table with the same name as the view and with columns of the same name in order to satisfy views that depend on this view. The table will be removed when the actual view is created. The properties of each column, aside from the data type, are not preserved in this temporary table, because they are not necessary.
[23 Jun 2006 3:44]
Wolfgang Fahl
The underlying problem is still there - views are not dumped correctly.
I find statements like this in my dump files:
INSERT INTO `gv1_userroleview` (`oid`, `name`) VALUES ('e6e4829555a68a302297d294c2361473','Develope
r'),('394d7a7d574ac0fb9f648ba36a776c73','CTS Administrator'),('97dc632de682a71e4cfcc13d10e0ac90','R
adiotherapy QA Administrator'),('0a83b7595b13ec3067eb8e69514f8383','Process user'),('46582562305623
45623789562348','Public');
but userroleview is a view ... only the underlying table data should be used for inserts - otherwise there will be double entries.
Please don't close this bug too quickly - I consider the view feature only half heartedly implemented as of 5.0 up to 5.0.22 - this is too bad since it is one of the most useful features that you can have in a database. Ask Trudy Pelzer and/or Peter Guluzan - they have had a good implementation of this feature for quite a while in their Ocelot SQL product - it's good that they now joint mySQL because they might bring in the necessary knowledge about proper view handling
[23 Jun 2006 7:29]
Georgi Kodinov
Thank you once again for taking the time to report a problem. I closed the bug as I wasn't able to reproduce the problem described in it in the way it was described. Please provide me with concrete steps to reproduce the errors you're observing (like for example a SQL script to create the database objects, exact command line of mysqldump used and exact mysqldump and mysql server versions) and I will do my best to fix them.
[23 Jun 2006 7:49]
Wolfgang Fahl
I'm sorry but you are asking too much cooperation from me here. I kindly ask you to take more responsibility for your product and make sure it fits the SQL specification. A View is not a Table and therefore it should be handled as a view. So try this: create a table; insert data into the table; create a view based on the table; create a dump of the database; look at the dump. The dump should not have insert statements for the view but only for the table. I've shown you a example that the dump has insert statements for the view. This is a severe bug as fare as I can see. It is critical since it will damage database consistency. I had the problem yesterday - i was not able to recreate a database from a backup dump without editing the sql file dumped. The time I had to take to fix the problem is now unfortunately not available any more for cooperating with you - I'm going on holiday in an hour or so. I'd be happy to see a fixed version with the solution of the problem when I'm back in a few weeks. Again - I trust in the knowledge of your company and the willingness to create a product according to SQL specs.
[7 Aug 2006 13:38]
[ name withheld ]
Can somebody please fix this? It is so trivial to re-create: 1) create some VIEW 'myview', make sure 'SELECT * from myview' returns some data 2) run mysqldump -c -n -t <database> 3) observe that data from the view is dumped in step 2 as if it was a regular table
[7 Aug 2006 14:02]
Georgi Kodinov
I was able to repeat using the steps provided : + drop table if exists t1; + Warnings: + Note 1051 Unknown table 't1' + create table t1 (a int); + insert into t1 values (1), (2); + drop view if exists v1; + Warnings: + Note 1051 Unknown table 'test.v1' + create view v1 as select * from t1; + select * from v1; + a + 1 + 2 + -- MySQL dump 10.10 + -- + -- Host: localhost Database: test + -- ------------------------------------------------------ + -- Server version 5.0.25-debug-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 */; + /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; + /*!40103 SET TIME_ZONE='+00:00' */; + /*!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 */; + + -- + -- Dumping data for table `t1` + -- + + LOCK TABLES `t1` WRITE; + /*!40000 ALTER TABLE `t1` DISABLE KEYS */; + INSERT INTO `t1` (`a`) VALUES (1),(2); + /*!40000 ALTER TABLE `t1` ENABLE KEYS */; + UNLOCK TABLES; + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + + /*!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 */; +
[7 Aug 2006 14:04]
Wolfgang Fahl
Great - it's looks like 5.0.24 does not have the problem any more
[7 Aug 2006 14:08]
Georgi Kodinov
There still exists the problem reported by frantisekb@roamsecure.net. I have just reproduced it using 5.0.25 BK. So I'm setting status to "Verified".
[7 Aug 2006 15:37]
Georgi Kodinov
It is indeed not a bug anymore. I've overlooked the fact that t1,not v1 is mentioned in the dump.
[28 Jul 2008 1:10]
Gregory Haase
Is it possible there was a regression in this bug to 5.1.24? I am experience the exact same error for the exact same circumstances as described in the original post.
[28 Jul 2008 7:03]
Georgi Kodinov
Gregory, Not sure what you mean here. If the bug is still reproducible in the latest 5.1 or 5.0 then please provide evidence to this and re-open the bug (or file another one if the problem is not exactly the same).
