321645 - [MySQL 5] MySql Error on insert... "DBD::mysql::st execute failed: Field 'status_whiteboard' "....

Status: RESOLVED FIXED

(Bugzilla :: Database, defect)

Description

[david m. chinn]

User-Agent:       Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)
Build Identifier: 2.20 downloaded from www.bugzilla.org on 10/21/2005

I was moving bugzilla servers from a MySql 5.0 Database to another MySql5.0 database.  restored with mysql

most things worked fine; search, update, etc.  It failed on trying to create a new bug.




Reproducible: Always

Steps to Reproduce:
1) backed up the bugzilla database (--all-databases, actually) using mysqldump.  If someone is interested, i could supply the resultant db restore script.

2) took the restore script and restored to mysql; logged into mysql as root and then "source db_restore_script"
Actual Results:  
sql error message... something like:

Error is something like, but not exactly:

DBD::mysql::st execute failed: Field 'status_whiteboard' doesn't have a 
default value [for Statement "INSERT INTO bugs 
(version,rep_platform,bug_severity,priority,op_sys,assigned_to,bug_status,e­verconfirmed,bug_file_loc,short_desc,target_milestone,product_id,component_­id, 
reporter, delta_ts, estimated_time, remaining_time, deadline) VALUES 
('Unspecified','PC','normal','P2','Windows','1','NEW','1','','test','---','­2','4',1,...

Expected Results:  
should have let me insert a bug

Using mysql 5.0
Have been using mysql 5.0 for some time now, since november
Have been able to enter over 200 bugs thus far
Insert problem only happens on a restored database
mysqldump options:
mysqldump.exe --all-databases --add-drop-table --add-drop-database --flush-logs --lock-tables --opt --verbose -uroot -pPASSWORD > sqlRestoreScript

Comment 1

[Max Kanat-Alexander]

Wow, I have no idea. That sounds like something went quite wrong with your mysqldump. Perhaps there's a bug in mysqldump in 5.0 on Windows.

Comment 2

[david m. chinn]

So, having a write only backup is kind of a bad thing, so I installed MySql 4.1 on the server I was moving bugzilla to.

I restored Bugzilla using the same dump script, and the problem went away.  Maybe it's a 5.0 problem?  Incidentally, googling for 'status_whiteboard restore mysql' turns up one hit... i.e... someone else had the same problem in november.

Comment 3

[Max Kanat-Alexander]

This is definitely strange. Could you possibly send me the mysqldump, mkanat -at- bugzilla.org?

Comment 4

[david m. chinn]

sorry max; 

bugzilla is a critical piece to the work around here. We couldn't afford the risk of having a write-only backup, and so we had to get rid of the v5 mysql and re-install 4.x.

I did find one news article with the same error message...
http://groups.google.com/group/netscape.public.mozilla.webtools/browse_thread/thread/88c7a57d7ec17698/625ce0b580930455?lnk=st&q=execute-failed+status_whiteboard&rnum=2&hl=en#625ce0b580930455

it suggested making the backup without --opt.  I don't remember if I tried this.

Here is the script I used to create the dump:
mysqldump.exe --all-databases --complete-insert --opt -ubugs -pvim342Sigmend > MySqlBackup

If you tell me that nobody over there can repro this problem, I'll look into reproing the problem on a test machine.

Comment 5

[Olav Vitters]

(In reply to comment #4)
> Here is the script I used to create the dump:

That contains a password. Make sure you do not use it anymore.

Comment 6

[Max Kanat-Alexander]

Okay, I have our answer, I believe, from bug 285824 comment 5: TEXT and BLOB types in MySQL 5 don't support "DEFAULT," apparently.

Now, that's ridiculous, since they sure seemed to work in MySQL 4.

Comment 7

[david m. chinn]

A) Glad you were able to repro the bug.
B) Thanks for noticing my gaff re: the password... I'm dangerous and should not be allowed out! :)

Comment 8

[Chaim Kleinhendler]

I have encountered a similar problem when importing bugzilla 2.18 data from sql 4.1 into Sql 5 and running the checksetup.pl to upgrade to bugzilla 2.20.1

I found out that when the new fields were added to the bugs table, the not null attribute for the status_whiteboard field was checked.
The same problem was with the keywords field. When i unchecked these attributes everything works fine.

I have used MySql Administrator to explore the database, and under catalogs, used the Table editor to change the attribute.

Comment 9

[rich_1219]

This appears to be mysql bug # 19498
http://bugs.mysql.com/bug.php?id=19498

Comment 10

[Christophe Naslain]

I also get this problem while migrating from a 2.19.2 to a 2.22. I first thought it was cos of my database content. But it's a migration problem.
Step by step:
- Installation of a fresh bugzilla 2.19.2 version on a MySQL 4.x
- Add a bug
- Dump the database
- Import the dump into a MySQL 5.0 database server
- Installation of a fresh bugzilla 2.22 version on MySQL 5.x
- Copy /data and localconfig files from 2.19.2
- Run checksetup.pl (localconfig corrections)
- re-run checksetup.pl

Software error:

DBD::mysql::st execute failed: Field 'status_whiteboard' doesn't have a default value [for Statement "INSERT INTO bugs (version,rep_platform,bug_severity,priority,op_sys,assigned_to,bug_status,everconfirmed,bug_file_loc,short_desc,target_milestone,product_id,component_id, reporter, delta_ts, estimated_time, remaining_time, deadline) VALUES ('other','PC','normal','P2','Linux','1','NEW','1','','Test code unchanged import 2.19.2','---','1','1',1, '2006-05-23 10:15:12', 0, 0, NULL)"] at Bugzilla/DB.pm line 84
	Bugzilla::DB::SendSQL('INSERT INTO bugs (version,rep_platform,bug_severity,priority,...') called at /home/httpd/bugzilla222/post_bug.cgi line 406


A (bad) workaround can be to update the 'bugs' table and allow the NULL value:
"ALTER TABLE `bugs`
CHANGE `status_whiteboard` `status_whiteboard` MEDIUMTEXT NULL ,
CHANGE `keywords` `keywords` MEDIUMTEXT NULL;"

Do anyone knows about the impact of this change in bugzilla?
Is there another way to dump/import the bugzilla data from MySQL 4.x to MySQL 5.x?

Comment 11

[Christophe Naslain]

Solution found by our database specialist: Remove the strict compatibility of MySQL 5.x from the my.ini file

# Set the SQL mode to strict
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Everything works fine now.

Comment 12

[hullabalooza09]

Does that mean, that this Bug is fixed and there are no more problems using Bugzilla with MySQL 5.x?

What consequences does this change to my.ini have for Bugzilla as well as for other applications.

Comment 13

[Juergen Neuhoff]

Using phpmyadmin for MySQL 5.0.22, we changed the 'bugs.status_whiteboard' and 'bugs.keywords' so that NULL values are allowed. 

Also, we changed the 'post_bug.cgi' script on line 248, as follows:

old line 248: 
       if ($keyword eq '') {

new line 248:
       if (empty($keyword eq '')) {

This seems to solve the problem.

Comment 14

[Gareth Pye]

I followed the instructions that you sugested juergen and it changed the error to:

undef error - Can't find param named keywords at Bugzilla/Config.pm line 171.

Also I can't manage to undo the changes to the database :)

Comment 15

[Olav Vitters]

*** Bug 358229 has been marked as a duplicate of this bug. ***

Comment 16

[Max Kanat-Alexander]

*** Bug 358394 has been marked as a duplicate of this bug. ***

Comment 17

[bigstijn]

Maybe it's a good idea to announce/publish already this 'bug' on the current release notes (eg. http://www.bugzilla.org/releases/2.22.1/release-notes.html), as a note for MySQL Users, as more and more (new) users will encounter this error.

The problem will occur with MySQL 5.0.25 and MySQL 5.1.12 or higher, also when doing a new installation.

1) Change was done in 5.0.25: quoting http://bugs.mysql.com/bug.php?id=19498
Noted in 5.0.25 changelog.
TEXT and BLOB columns do not support DEFAULT values. However, when a
default of '' was specified, the specification was silently ignored.
This now results in a warning, or an error in strict mode.

2) Having default values for TEXT is now a change request for mysql: see http://bugs.mysql.com/bug.php?id=21532
--> it can take some time before it's actually implemented.

3) Probably, from dev-versions 5.1.12 on, this bug will also occur on 5.1.X versions: http://dev.mysql.com/doc/refman/5.1/en/news-5-1-12.html

Comment 18

[Olav Vitters]

I can reproduce now my distro now has 5.0.27. Taking.

Comment 19

[Olav Vitters]

*** Bug 360587 has been marked as a duplicate of this bug. ***

Comment 20

[Olav Vitters]

Attachment: Patch v1

Removes the following options from sql_mode:
 STRICT_TRANS_TABLES / STRICT_ALL_TABLES / TRADITIONAL

The strict ones cause strict mode to be set. Traditional sets at least the other two options again, so that one also has to go.

Note that in older MySQL versions, SELECT @@sql_mode will return "" (not NULL); tested on 4.1.20.

Also tried a patch that wouldn't set the defaults, but after a while I gave up. This because Bugzilla is designed that these TEXT/BLOB types have defaults. Bugzilla therefore doesn't always include these fields in an INSERT statement.

Comment 21

[Max Kanat-Alexander]

Comment on attachment 245509 [details] [diff] [review]
Patch v1

Yeah, this is OK. But it needs a better comment above the regex, when you check it in.

Comment 22

[Olav Vitters]

tip:

Checking in Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.42; previous revision: 1.41
done

2.22:

Checking in Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.36.2.3; previous revision: 1.36.2.2
done

And of course I forgot the better comment. I'll add that shortly.

Comment 23

[GavinS]

My MySQL version 4.0.16 on OSX now gives the following message from checksetup.pl:

There was an error connecting to MySQL:

    Unknown system variable 'sql_mode'

The latest docs foor cvs tip say I need MySQL 4.0.14.

Has the version number  been bumped by this change?

Comment 24

[GavinS]

Re-opening - hope that's OK...

2 problems as I see it:

1) sql_mode setting seems to be in 4.1 (http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html)

2) and the new sql_mode setting seems to happen before we do the MySQL version check, so we get an error message, instead of the nicer old-version message

Comment 25

[Olav Vitters]

Attachment: Patch v2

(In reply to comment #24)
> 2 problems as I see it:
> 
> 1) sql_mode setting seems to be in 4.1
> (http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html)

From everything I read I really thought it was 5.0 only :-(

> 2) and the new sql_mode setting seems to happen before we do the MySQL version
> check, so we get an error message, instead of the nicer old-version message

I wanted to avoid a version check. This new version shouldn't cause any problems anymore. Please test. SHOW VARIABLES is like SQL -- if the variable doesn't exist it doesn't return any rows. I wanted to do this initially (after mkanat suggested it), however switched to @@sql_mode as it avoided the non-use of $var -- plus that I thought it worked with older versions.

Comment 26

[GavinS]

Comment on attachment 245690 [details] [diff] [review]
Patch v2

My checksetup works fine with this patch - thanks.

(Could any checkin also fix the typo in the following comment: 'STICT' )

Comment 27

[Frédéric Buclin]

dave, please re-approve this bug (patch #2 this times).

Comment 28

[Olav Vitters]

tip:

Checking in Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.44; previous revision: 1.43
done

2.22:

Checking in Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.36.2.5; previous revision: 1.36.2.4
done

Typo has been fixed as well.

Comment 29

[Frédéric Buclin]

*** Bug 362195 has been marked as a duplicate of this bug. ***

Comment 30

[Martin Mokrejs]

I can confirm the bug appears even on clean install on mysql-5.0.26 and these two patches fix it.

Comment 31

[Frédéric Buclin]

*** Bug 363520 has been marked as a duplicate of this bug. ***

Comment 33

[jessn]

I have had the same problem with defaults at MqSql 5 in general. It seems like MySql have had major changes in version 5.x. What so ever I'm able to use the latest of the version 4 series. 

Currently I'm using v4.1.

Comment 34

[Bradley Baetz (:bbaetz)]

Is there a bug on not requiring strict mode to be disabled (where strict == 'not-making-up-data'...)?

('Traditional' mode is defined as: 'A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column.')

Also, I may be missing something, but how does this solve the backup problem originally reported? Isn't that a MySQL bug, that a backup fails to restore, regardless of what bugzilla may be doing?

Comment 35

[Olav Vitters]

(In reply to comment #34)
> Is there a bug on not requiring strict mode to be disabled (where strict ==
> 'not-making-up-data'...)?

See comment 17. MySQL doesn't support a default for TEXT/BLOB stuff, while Bugzilla expects that. The fix is to have MySQL support it.

> Also, I may be missing something, but how does this solve the backup problem
> originally reported? Isn't that a MySQL bug, that a backup fails to restore,
> regardless of what bugzilla may be doing?

What backup problem? That the initial reporter used mysqldump is not relevant. The problem is MySQL >=5.0.25 giving an error with strict mode. The way you upgrade (restoring a dump to a 5.0.25+ MySQL or just upgrading MySQL) doesn't really matter.
There is another backup/restore problem that I am aware of, but that error message is not mentioned in this bug (ALTER TABLE will fail). Further, it is a separate bug.

Comment 36

[Zeke Weeks]

I just upgraded my MySQL Server installation to v5.0.37-community-nt from v5.0.27-community-nt yesterday and received the error found in the title when submitting a new bug report today.  I am running Bugzilla 2.22.2 on a Windows XP Professional PC (It has been working great for ~ 2 years & 440+ bugs)

To resolve, I applied the suggestions found in Comment #13.  I'm unsure if this is the correct solution to this problem as it appears as though recent patches would have fixed this problem?

Please excuse me if I did not follow proper bug reporting protocols...I'm new to this list.

Comment 37

[jessn]

(In reply to comment #13)
> Using phpmyadmin for MySQL 5.0.22, we changed the 'bugs.status_whiteboard' and
> 'bugs.keywords' so that NULL values are allowed. 
> 
> Also, we changed the 'post_bug.cgi' script on line 248, as follows:
> 
> old line 248: 
>        if ($keyword eq '') {
> 
> new line 248:
>        if (empty($keyword eq '')) {
> 
> This seems to solve the problem.

I am wondering if this the only place that should be modified. Does this modificatation really fixes all the issues reported in Bug 366711?

Comment 38

[Max Kanat-Alexander]

  Hey Jessn. This bug was marked FIXED in 2006, so if there were any other issues since then, they would have been filed as separate bugs.