Bug #14061 mysqldump fails when dumping view

• Submitted: 16 Oct 2005 20:54
• Modified: 31 Oct 2005 20:02
• Reporter: Markus Popp
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.0.13-rc-nt-max/5.0.16 BK
• OS: Windows (Windows XP SP 1/Linux)
• Assigned to: Bugs System
• CPU Architecture: Any

[16 Oct 2005 20:54] Markus Popp

Description:
Dumping a database containing a view, I get the error message:

mysqldump: mysqldump: Couldn't execute 'CREATE TEMPORARY TABLE `v_php_cert_answers` SELECT * FROM `v_php_cert_answers` WHERE 0': Invalid default value for 'answer_letter' (1067)

How to repeat:
Create following tables and view:

CREATE TABLE php_cert_answers(
id int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
chapter int( 10 ) unsigned NOT NULL ,
qu_no int( 10 ) unsigned NOT NULL ,
answer_letter enum( 'a', 'b', 'c', 'd', 'e', 'f', 'g' ) NOT NULL ,
answer text,
PRIMARY KEY ( id ) ,
UNIQUE KEY chapter( chapter, qu_no, answer_letter ) ,
KEY chapter_2( qu_no ) 
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE php_cert_questions(
id int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
chapter int( 10 ) unsigned NOT NULL ,
qu_no int( 10 ) unsigned NOT NULL ,
question text NOT NULL ,
correct_answer set( 'a', 'b', 'c', 'd', 'e', 'f', 'g' ) default NULL ,
answer_text text,
PRIMARY KEY ( id ) ,
UNIQUE KEY chapter( chapter, qu_no ) 
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

ALTER TABLE `php_cert_answers` ADD CONSTRAINT php_cert_answers_ibfk_1 FOREIGN KEY ( chapter ) REFERENCES php_cert_questions( chapter ) ,
ADD CONSTRAINT php_cert_answers_ibfk_2 FOREIGN KEY ( chapter, qu_no ) REFERENCES php_cert_questions( chapter, qu_no ) ;

CREATE VIEW v_php_cert_answers AS
select a.id, a.chapter, a.qu_no, a.answer_letter,
if((find_in_set(a.answer_letter, b.correct_answer) > 0), 'r', 'f') AS is_correct,
a.answer
from php_cert_answers a left join php_cert_questions b on a.chapter = b.chapter and a.qu_no = b.qu_no
order by a.chapter,a.qu_no,a.answer_letter;

Dumping the database, the error occurs:

E:\>mysqldump --add-drop-table --database test > test.sql
Enter password: *******
mysqldump: mysqldump: Couldn't execute 'CREATE TEMPORARY TABLE `v_php_cert_answers` SELECT * FROM `v_php_cert_answers` WHERE 0': Invalid default value for 'answer_letter' (1067)

E:\>

[17 Oct 2005 8:07] Sergei Golubchik

the fix for bug#12838 should help here too.

[17 Oct 2005 12:32] MySQL Verification Team

Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysqldump -uroot --add-drop-table --database test > test.sql
mysqldump: mysqldump: Couldn't execute 'CREATE TEMPORARY TABLE `v_php_cert_answers` SELECT * FROM `v_php_cert_answers` WHERE 0': Invalid default value for 'answer_letter' (1067)
miguel@hegel:~/dbs/5.0>

[17 Oct 2005 19:35] Patrick Galbraith

see fix for 12838

[22 Oct 2005 1:25] 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/internals/31315

[27 Oct 2005 9:20] Jon Stephens

Need 3-part version number so we know which changelog to document fix in. Thanks.

[31 Oct 2005 16:21] Patrick Galbraith

fixed in 5.0.15

[31 Oct 2005 20:02] Paul DuBois

Noted in 5.0.15 changelog.