Bug #14061 mysqldump fails when dumping view
Submitted: 16 Oct 2005 20:54 Modified: 31 Oct 2005 20:02
Reporter: Markus Popp Email Updates:
Status: Closed Impact on me:
None 
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.