Bug Report 67 - SQL Error when adding new admin on installation

Bug Id

Reporter

Everah

Product/Version

Bugdar / 1.1.5

Status

Closed

Severity

Moderate

Duplicate Of

- none -

Fixed in Revision

1.2.x/2c8c00e, trunk/fa15511

Mstone

Summary

SQL Error when adding new admin on installation

Report Time

March 7, 2007 08:14 AM

Assignment

Robert

Resolution

Fixed

Priority

Address Now

Dependencies

- none -

Mstone (old)

Attachments

patch-67.diff Patch

Votes

For: 0 (0%)

Against: 0 (0%)

Total: 0

March 7, 2007 08:14 AM Everah

When attempting to install Bugdar on my local server I get to the step where the installer sets up the global administrator. It took the information, pushed 'User Added' to the screen then threw this SQL error:

Error: Database Error in `Bugdar`

Ã‚Â» Query:

INSERT INTO user (email,displayname,password,showcolors,usergroupid,salt,authkey) VALUES ('robert@everah.com','Robert Gonzalez','896b1837ed98036588d884516d1d1650','1',6,'SVbv9','w0E5Wf2pt3DmCz4wyx7Xum1ZozCp8V2rzfwqzXkvrK8A1aBBdwvJay')

Ã‚Â» Error Number: 1364
Ã‚Â» Error Message: Field 'showemail' doesn't have a default value
Ã‚Â» Additional Notes: Invalid SQL query
Ã‚Â» File: /install/install.php

I am also noticing that the template parser seems to not be parsing correctly. This is the source of this particular step:
============================================================
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<title><?= $GLOBALS['isso:callback']->getApplication() ?> Installer</title>

<style type="text/css">

</style>
</head>
<body>

<h1>User Added</h1>

Your new user has been added.

 
<table cellpadding="4" cellspacing="1" border="0" width="500" style="background-color: #990000; color: black; font-family: Verdana, sans-serif; font-size: 12px;">
<tr style="color: #EFEFEF; text-align: left">
<td>Error: Database Error in `Bugdar`</td>
</tr>
<tr style="background-color: #FFFFFF; text-align: left">
<td><blockquote>

» Query:

 <pre style="font-family: 'Courier New', Courier, mono; font-size: 11px;">INSERT INTO user (email,displayname,password,showcolors,usergroupid,salt,authkey) VALUES ('robert@everah.com','Robert Gonzalez','896b1837ed98036588d884516d1d1650','1',6,'SVbv9','w0E5Wf2pt3DmCz4wyx7Xum1ZozCp8V2rzfwqzXkvrK8A1aBBdwvJay')</pre>
 
» Error Number: 1364
 
» Error Message: Field 'showemail' doesn't have a default value

 
» Additional Notes: Invalid SQL query
 
» File: /install/install.php


</blockquote></td>

</tr>
</table>
 
============================================================
I am not really sure where I am in the set up (thought I am going to tinker with this). I would like to implement this on a corporate scale and am evaluating this app for that reason.

Current set up on my local server:
OS: WIN XP Pro SP2
Server: Apache 2.0.55
PHP: 5.1.2
MySQL: 5.0.18
Browser: Firefox 2.0.2

March 7, 2007 08:26 AM Everah

Ok, after this issue, I copied the SQL from the error and entered it manually through my client interface. I then forced the next step by navigating to install.php?mark=7 and everything seemed to work OK from there.

NOTE: I actually forced the navigation, found that there was no user in the database, then copied the SQL from here and executed it in the client. I was then able to log in without incident.

March 7, 2007 06:01 PM Robert

This is interesting. I think you must be running MySQL in strict mode because it's checking to see if there are proper NULL values set on a MySQL column because I haven't seen this on my local installations. I'll be sure to fix all the table columns for the next release.

March 7, 2007 06:19 PM Everah

The weird thing about this is I took the exact same query that the app reported the error on, and without manipulating it, executed it in SQLYog and it took it just fine. Of course, SQLYog uses the MySQL client library, not the Client API that PHP uses, but it just seemed odd to me.

Anyway, thanks for looking into this, and thanks for the great product.

On March 17, 2007 05:39 PM, simplicity changed:

Summary from "SQL Error when adding new admin on installation" to ""
Severity from "Moderate" to "Trivial"
Product from "Bugdar" to ""
Version from "1.1.5" to ""

On March 17, 2007 06:24 PM, Robert changed:

Summary from "" to "SQL Error when adding new admin on installation"
Severity from "Trivial" to "Moderate"
Product from "" to "Bugdar"
Version from "" to "1.1.5"

March 19, 2007 03:26 AM Robert

Okay after playing around with numerous MySQL versions I cannot figure out what would cause this to happen. I've run this on MySQL 5.0.16, 5.1.16, and 4.1.22 and I cannot recreate this at all. If you can recreate this issue again, could you please do one of the following:

a) In phpMyAdmin, at the main screen click "Show MySQL system variables" and then copy that output and send it to me.

or

b) At the UNIX command line type (without quotes: "mysql -u<your MySQL username> -p". You will then be requested to enter your MySQL password. You will then be at another prompt where you type (again, without quotes): "SHOW VARIABLES;". Please copy this output and send it to me.

You can send the data to me here: http://www.bluestatic.org/about/

Maybe something's set in your system variables that's causing this but nothing pops out at me on my systems.

March 19, 2007 04:12 AM Everah

Thanks for the reply Robert. I sent you the variables list at the contact form. I used my other email address by mistake (robert at everah dot com) Sorry about that.

I haven't been in the code since I installed the app (a few minutes before the posting of this bug), but I did notices that the error mentioned a field named showemail that is not even in the insert query. Not sure how that works, but is there another query that runs before the insert?

March 19, 2007 04:14 AM Everah

And as for the parser not parsing templates... I have short tags off in my installation of PHP. Have you considered using long PHP tags in the script? Anyone that uses a PHP install (from 5 on) that is left in its default state will have the same issue as I did as short tags are off by default (I think... I am not certain about this though).

March 19, 2007 05:44 AM Robert

I think the SQL error is related to the fact that there's no default value specified in the schema for the user table and it's marked as NOT NULL. But I've never seen MySQL actually enforce that which is why this is so weird. I got your email and I'll start comparing variables to see if I can't find a setting that you have that could possibly cause this to happen.

And I'll definitely be fixing the use of short tags for 1.2.0 (which is--bar security problems--the next release). Thanks for figuring that out.

March 19, 2007 07:42 AM Robert

I've now compared your MySQL variables to mine and with the exception of a few packet size settings, directory locations, and language options they're identical files. I cannot think of anything that would cause this to happen but I'll do some more googling tomorrow.

April 2, 2007 05:51 PM Pvt_Ryan

I think this is caused by the permissions that the user used to install has..

I got the same / a similar error ( I didn't pay too much attention at the time ) I changed the users permissions and reinstalled, it worked fine. I think I had forgotten to grant DELETE to the db user on my bug database.

Under phpmyadmin look in the db "mysql" at the table "db" and check the permissions that the user you are using to access the bug db has..

April 2, 2007 05:54 PM Robert

That sounds like a definite possibility. I'll do some checking around on my system to see if I can replicate it under those circumstances. If so, then I'll simply add some checking in the install script to alert the admin that he/she needs to grant full privileges to the database user before installing.

April 2, 2007 06:09 PM Everah

I believe I was installing as root with full privileges. I am not at the machine that I set this up on so I can't be sure, but I will check and see when I get home.

April 3, 2007 05:03 AM Robert

I've added an installation check of MySQL permissions to the installer so that should stop this potential cause of the bug. But I'm still not convinced that the original error presented would be caused by this, it should have been an ACCESS DENIED error.

January 11, 2009 04:58 PM csd

I got the same error message while installing Bugdar v1.2.2 locally.

Furthermore I got the same type of error (Field 'somefield' doesn't have a default value) when attempted to add the first bug report. There are *lots* of fields without initial values (neither from the insert query nor by default) but with 'not null' constraints: duplicateof, dependency, hidden, initialreport, lastposttime, lastpostby, hiddenlastposttime, hiddenlastpost.

OS: WIN XP Pro SP2

Version Information
Bugdar Version 1.2.2
PHP Version 5.2.5
MySQL Version 5.0.51a-community-nt
Web Server Microsoft-IIS/5.1

Server Options
MySQL: Maximum Packet Size 1048576
MySQL: Maximum Upload Size 2097152
MySQL: Full-Text Search Minimum Word Length 4
PHP: Safe Mode No
PHP: Register Globals No
PHP: Magic Quotes GPC No
PHP: Magic Quotes Sybase No
PHP: Gettext Installed No
Browser: Firefox 2.0.2

January 11, 2009 05:52 PM Robert

Try running these two queries and report the result:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

January 12, 2009 01:23 AM csd

The result for the queries above:

'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

January 12, 2009 01:33 PM Robert

The problem is that STRICT_TRANS_TABLES option, which is what's causing this to happen. This patch will clear the sql_mode so that strict mode is no longer in effect.

On January 12, 2009 01:34 PM, Robert changed:

Status from "Unconfirmed" to "Confirmed"

January 15, 2009 12:13 PM pooh

solution for this problem is adding DEFAULT value for all NOT NULL columns in CREATE TABLE statements, see http://dev.mysql.com/doc/refman/5.1/en/create-table.html

January 15, 2009 04:06 PM Robert

That solution is not viable for a 1.2.x release. Removing strict mode is a far easier solution that will have far less implications, and strict mode may cause other problems.

January 23, 2009 01:32 AM Robert

csd, does the attached patch fix the issue for you?

On January 23, 2009 01:32 AM, Robert changed:

Priority from "Normal" to "Address Now"

On January 27, 2009 05:09 PM, Robert changed:

Status from "Confirmed" to "Assigned"

February 2, 2009 11:10 PM csd

Yes, it does, absolutely. Thanks a lot!

February 3, 2009 12:14 AM Robert

Glad that fixed it.

--------------- AUTOMATIC RESPONSE ---------------
Thank you for your bug report. This issue has been closed and fixed in git. This change will be available in a future release, but you can download the change at any time from the git repository, found at http://www.bluestatic.org/git/.

On February 3, 2009 12:14 AM, Robert changed:

Status from "Assigned" to "Closed"
Resolution from "Open" to "Fixed"
Fixed in Revision from "" to "1.2.x/2c8c00e, trunk/fa15511"