| Bug #15186 | mysqldump fails when dumping "distinct" view | ||
|---|---|---|---|
| Submitted: | 23 Nov 2005 14:54 | Modified: | 24 Nov 2005 8:35 |
| Reporter: | Jorgen Norling | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.15 | OS: | Linux (Linux) |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[23 Nov 2005 15:14]
MySQL Verification Team
With current source server I was unable to repeat:
miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.17-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE DATABASE test_dump DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> use test_dump
Database changed
mysql> create table student(
-> student_id integer
-> ,student_name varchar(50)
-> ,student_type enum('full-time','part-time') not null
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> create or replace view v_student
-> as
-> select distinct student_name, student_type
-> from student
-> ;
Query OK, 0 rows affected (0.00 sec)
miguel@hegel:~/dbs/5.0> bin/mysqldump --opt test_dump > test_dump.sql
miguel@hegel:~/dbs/5.0> cat test_dump.sql
-- MySQL dump 10.10
--
-- Host: localhost Database: test_dump
-- ------------------------------------------------------
-- Server version 5.0.17-debug
/*!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 */;
--
-- Table structure for table `student`
--
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`student_id` int(11) default NULL,
`student_name` varchar(50) default NULL,
`student_type` enum('full-time','part-time') NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `student`
--
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
LOCK TABLES `student` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
--
-- Table structure for table `v_student`
--
DROP TABLE IF EXISTS `v_student`;
/*!50001 DROP VIEW IF EXISTS `v_student`*/;
/*!50001 DROP TABLE IF EXISTS `v_student`*/;
/*!50001 CREATE TABLE `v_student` (
`student_name` varchar(50),
`student_type` enum('full-time','part-time')
) */;
--
-- View structure for view `v_student`
--
/*!50001 DROP TABLE IF EXISTS `v_student`*/;
/*!50001 DROP VIEW IF EXISTS `v_student`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_student` AS select distinct `student`.`student_name` AS `student_name`,`student`.`student_type` AS `student_type` from `student`*/;
/*!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 */;
[24 Nov 2005 8:35]
Jorgen Norling
I installed version 5.0.16 and then the failure was gone. Could this bug have been fixed when bug #12838 was fixed?
Description: mysqldump fails with error message... Couldn't execute 'CREATE TEMPORARY TABLE x SELECT * FROM x WHERE 0': Invalid default value for 'y' (1067) ...if you backup a database which contain a view that includes "select distinct" on a table with a column of datatype enum that is defined not null. How to repeat: mysql> CREATE DATABASE 'test_dump' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.01 sec) mysql> use test_dump; Database changed mysql> create table student( -> student_id integer -> ,student_name varchar(50) -> ,student_type enum('full-time','part-time') not null -> ); Query OK, 0 rows affected (0.00 sec) mysql> create or replace view v_student -> as -> select distinct student_name, student_type -> from student -> ; Query OK, 0 rows affected (0.01 sec) mysql> exit Bye shell> mysqldump --opt test_dump > ../backup/test_dump.sql mysqldump: mysqldump: Couldn't execute 'CREATE TEMPORARY TABLE `v_student` SELECT * FROM `v_student` WHERE 0': Invalid default value for 'student_type' (1067)