[authlib] Why "id int(11) DEFAULT '0'"?

[Gilles Ganault]

Hello

(Sorry for asking this in a PHP ng, but I already asked in a MySQL
forum, but got no definitive answer):

I'm trying to install the authlib module that's required for
the open-source iOutliner web-based outliner (http://ioutliner.com),
but it fails halfway through when creating the tables:

//Bad! /* SQL Error: Invalid default value for 'id' */
$query = mysql_query("CREATE TABLE authlib_data (
id int(11) DEFAULT '0' NOT NULL auto_increment,
name text NOT NULL,
email text NOT NULL,
age int(3) DEFAULT '0' NOT NULL,
sex text NOT NULL,
school text NOT NULL,
PRIMARY KEY (id)
)");

//Bad! /* SQL Error: Invalid default value for 'id' */
$query = mysql_query("CREATE TABLE authlib_login (
id int(11) DEFAULT '0' NOT NULL auto_increment,
username text NOT NULL,
password text NOT NULL,
PRIMARY KEY (id)
)");

According to Google, DEFAULT '(0)' seems a legit instruction. Indeed,
if I remove this part, it creates the table.

But this error is found in both the authlib 1.96 that comes with
iOutliner and the original file on Sourceforge:

http://sourceforge.net/project/showf...group_id=20743

Could it be that it's a legal bit when MySQL is compiled in a
particular way?

Thank you.

[ZeldorBlat]

On Jun 8, 5:04 pm, Gilles Ganault <[e-mail]:

Hello

(Sorry for asking this in a PHP ng, but I already asked in a MySQL
forum, but got no definitive answer):

I'm trying to install the authlib module that's required for
the open-source iOutliner web-based outliner (http://ioutliner.com),
but it fails halfway through when creating the tables:

//Bad! /* SQL Error: Invalid default value for 'id' */
$query = mysql_query("CREATE TABLE authlib_data (
id int(11) DEFAULT '0' NOT NULL auto_increment,
name text NOT NULL,
email text NOT NULL,
age int(3) DEFAULT '0' NOT NULL,
sex text NOT NULL,
school text NOT NULL,
PRIMARY KEY (id)
)");

//Bad! /* SQL Error: Invalid default value for 'id' */
$query = mysql_query("CREATE TABLE authlib_login (
id int(11) DEFAULT '0' NOT NULL auto_increment,
username text NOT NULL,
password text NOT NULL,
PRIMARY KEY (id)
)");

According to Google, DEFAULT '(0)' seems a legit instruction. Indeed,
if I remove this part, it creates the table.

But this error is found in both the authlib 1.96 that comes with
iOutliner and the original file on Sourceforge:

http://sourceforge.net/project/showf...group_id=20743

Could it be that it's a legal bit when MySQL is compiled in a
particular way?

Thank you.

Remove the quotes around 0.

[Gilles Ganault]

On Fri, 08 Jun 2007 21:19:11 -0000, ZeldorBlat <ze********@gmail.com>
wrote:

>Remove the quotes around 0.

It doesn't work any better:

mysqlCREATE TABLE authlib_data (id int(11) DEFAULT 0 NOT NULL
auto_increment,age int(3) DEFAULT 0 NOT NULL, PRIMARY KEY (id));

ERROR 1067 (42000): Invalid default value for 'id'

Thanks.

[Denis Gerina]

Gilles Ganault wrote:

Hello

(Sorry for asking this in a PHP ng, but I already asked in a MySQL
forum, but got no definitive answer):

I'm trying to install the authlib module that's required for
the open-source iOutliner web-based outliner (http://ioutliner.com),
but it fails halfway through when creating the tables:

//Bad! /* SQL Error: Invalid default value for 'id' */
$query = mysql_query("CREATE TABLE authlib_data (
id int(11) DEFAULT '0' NOT NULL auto_increment,
name text NOT NULL,
email text NOT NULL,
age int(3) DEFAULT '0' NOT NULL,
sex text NOT NULL,
school text NOT NULL,
PRIMARY KEY (id)
)");

//Bad! /* SQL Error: Invalid default value for 'id' */
$query = mysql_query("CREATE TABLE authlib_login (
id int(11) DEFAULT '0' NOT NULL auto_increment,
username text NOT NULL,
password text NOT NULL,
PRIMARY KEY (id)
)");

According to Google, DEFAULT '(0)' seems a legit instruction. Indeed,
if I remove this part, it creates the table.

But this error is found in both the authlib 1.96 that comes with
iOutliner and the original file on Sourceforge:

http://sourceforge.net/project/showf...group_id=20743

Could it be that it's a legal bit when MySQL is compiled in a
particular way?

Thank you.

DEFAULT and auto_increment on the same column? Why, why, oh my dear God
why? :)

[Gilles Ganault]

On Fri, 08 Jun 2007 23:44:46 +0200, Denis Gerina <de***@cced.ba>
wrote:

>DEFAULT and auto_increment on the same column? Why, why, oh my dear God
why? :)

Same here ;-)

I removed DEFAULT '0' from the ID key. I'll see if that's a problem.

I'm stuck further down the road. This library is a total mess :-/ The
joys of open-source...

[Jerry Stuckle]

Gilles Ganault wrote:

Hello

(Sorry for asking this in a PHP ng, but I already asked in a MySQL
forum, but got no definitive answer):

I'm trying to install the authlib module that's required for
the open-source iOutliner web-based outliner (http://ioutliner.com),
but it fails halfway through when creating the tables:

//Bad! /* SQL Error: Invalid default value for 'id' */
$query = mysql_query("CREATE TABLE authlib_data (
id int(11) DEFAULT '0' NOT NULL auto_increment,
name text NOT NULL,
email text NOT NULL,
age int(3) DEFAULT '0' NOT NULL,
sex text NOT NULL,
school text NOT NULL,
PRIMARY KEY (id)
)");

//Bad! /* SQL Error: Invalid default value for 'id' */
$query = mysql_query("CREATE TABLE authlib_login (
id int(11) DEFAULT '0' NOT NULL auto_increment,
username text NOT NULL,
password text NOT NULL,
PRIMARY KEY (id)
)");

According to Google, DEFAULT '(0)' seems a legit instruction. Indeed,
if I remove this part, it creates the table.

But this error is found in both the authlib 1.96 that comes with
iOutliner and the original file on Sourceforge:

http://sourceforge.net/project/showf...group_id=20743

Could it be that it's a legal bit when MySQL is compiled in a
particular way?

Thank you.

You got a perfectly good (and perfectly correct) response from Willem
Bogaerts in comp.database.mysql with in 24 minutes of your original
posting. You even responded to it.

If you didn't like the answer, you should have requested a clarification
instead of taking to to another newsgroup where it's off-topic.

Or, since it's from Sourceforge, you should have asked in the project
support.

This is NOT the place to be asking mysql questions. That's why
comp.databases.mysql was created.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Geoff Berrow]

Message-ID: <qf********************************@4ax.comfrom Gilles
Ganault contained the following:

>On Fri, 08 Jun 2007 21:19:11 -0000, ZeldorBlat <ze********@gmail.com>
wrote:

>>Remove the quotes around 0.

It doesn't work any better:

mysqlCREATE TABLE authlib_data (id int(11) DEFAULT 0 NOT NULL
auto_increment,age int(3) DEFAULT 0 NOT NULL, PRIMARY KEY (id));

ERROR 1067 (42000): Invalid default value for 'id'

Sheesh, I'm no sql whizz but even I can work out why that is nonsense.
auto_increment works in a similar way as DEFAULT adding a value if one
is not set in the sql except with auto_increment (and the clue is in the
name folks) a new unique number is added to the new row. Having both
must surely conflict and besides, DEFAULT would be redundant
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[ZeldorBlat]

On Jun 9, 7:30 am, Geoff Berrow <[e-mail]:

Message-ID: <qf********************************@4ax.comfrom Gilles
Ganault contained the following:

On Fri, 08 Jun 2007 21:19:11 -0000, ZeldorBlat <[e-mail]>
wrote:

>Remove the quotes around 0.

It doesn't work any better:

mysqlCREATE TABLE authlib_data (id int(11) DEFAULT 0 NOT NULL
auto_increment,age int(3) DEFAULT 0 NOT NULL, PRIMARY KEY (id));

ERROR 1067 (42000): Invalid default value for 'id'

Sheesh, I'm no sql whizz but even I can work out why that is nonsense.
auto_increment works in a similar way as DEFAULT adding a value if one
is not set in the sql except with auto_increment (and the clue is in the
name folks) a new unique number is added to the new row. Having both
must surely conflict and besides, DEFAULT would be redundant

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDshttp://www.ckdog.co.uk/rfdmaker/

Doh! I should have seen that, too. Thanks Geoff.

[Gilles Ganault]

On Fri, 08 Jun 2007 22:57:13 -0400, Jerry Stuckle
<js*******@attglobal.netwrote:

>You got a perfectly good (and perfectly correct) response from Willem
Bogaerts in comp.database.mysql with in 24 minutes of your original
posting. You even responded to it.

Sorry, before making changes to the installer, I wished to get a
second opinion. Like going to the doctor, you know.

[Gilles Ganault]

On Sat, 09 Jun 2007 12:30:08 +0100, Geoff Berrow
<bl******@ckdog.co.ukwrote:

>Sheesh, I'm no sql whizz but even I can work out why that is nonsense.

I know that, but it just seemed very strange that this error was found
in the official package, and used as is in an application I was trying
to install. If that's correcxt, then how come it doesn't work in MySQL
4 and 5?

I ended removing DEFAULT '0' for the ID. Hopefully, that won't break
the app somewhere down the line.

Thank you.

[Jerry Stuckle]

Gilles Ganault wrote:

On Fri, 08 Jun 2007 22:57:13 -0400, Jerry Stuckle
<js*******@attglobal.netwrote:

>You got a perfectly good (and perfectly correct) response from Willem
Bogaerts in comp.database.mysql with in 24 minutes of your original
posting. You even responded to it.

Sorry, before making changes to the installer, I wished to get a
second opinion. Like going to the doctor, you know.

Then ask for another opinion in comp.databases.mysql. You want to get a
second opinion for your doctor, you don't go to an auto mechanic, do
you? Ir if you're asking for a second opinion for a cardiologist, you
don't go to an orthopedic surgeon.

This group is for PHP questions, not MySQL ones. Now please take your
questions to a more appropriate newsgroup.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Geoff Berrow]

Message-ID: <kr********************************@4ax.comfrom Gilles
Ganault contained the following:

>On Sat, 09 Jun 2007 12:30:08 +0100, Geoff Berrow
<bl******@ckdog.co.ukwrote:

>>Sheesh, I'm no sql whizz but even I can work out why that is nonsense.

I know that, but it just seemed very strange that this error was found
in the official package, and used as is in an application I was trying
to install. If that's correcxt, then how come it doesn't work in MySQL
4 and 5?

I can only assume that it is ignored in other versions.

>
I ended removing DEFAULT '0' for the ID. Hopefully, that won't break
the app somewhere down the line.

If DEFAULT '0' was doing anything, the application would be seriously
broken anyway. DEFAULT is used to insert a default value if none is
specified. But this column is the only primary key! It is not used in
conjunction with any other column. The primary key MUST be unique and
therefore cannot have a default value.

The only thing that might make sense is to have a starting value for the
auto-increment but you would set this in another way.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/