Error in CREATE TABLE

I am having error trying to run some mysql script (back from 2001). It
gives me the error like :

ERROR 1067 (42000) at line 48: Invalid default value for 'log_id'

in the statement :

CREATE TABLE Log (
log_id int(11) DEFAULT '0' NOT NULL auto_increment,
source_id smallint(6) DEFAULT '0' NOT NULL,
l_time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
l_nanos int(11) DEFAULT '0' NOT NULL,
l_message text,
PRIMARY KEY (log_id)
);

What is this? I am not familiar with mysql, the script is part of
another installation script for the program made in 2001 by the
company that no longer exists. We needed to upgrade the machine the
program was running (redhat 8) to the RedHat ES 4. Now the installation
script gives us this error.

Please help.

Re: Error in CREATE TABLE

wrote in message
news:1142609339.130712.264160@j33g2000cwa.googlegroups.com.. .
> I am having error trying to run some mysql script (back from 2001). It
> gives me the error like :
>
> ERROR 1067 (42000) at line 48: Invalid default value for 'log_id'
>
> in the statement :
>
> CREATE TABLE Log (
> log_id int(11) DEFAULT '0' NOT NULL auto_increment,
> source_id smallint(6) DEFAULT '0' NOT NULL,
> l_time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
> l_nanos int(11) DEFAULT '0' NOT NULL,
> l_message text,
> PRIMARY KEY (log_id)
> );
>
> What is this? I am not familiar with mysql, the script is part of
> another installation script for the program made in 2001 by the
> company that no longer exists. We needed to upgrade the machine the
> program was running (redhat 8) to the RedHat ES 4. Now the installation
> script gives us this error.

Your error message claims MySQL to be choking on line #48 of this 8 line
script!

I suspect you are failing to notice text falling off your screen somewhere
beyond this short CREATE TABLE statement.

Scroll down!
Thomas Bartkus

Re: Error in CREATE TABLE

ilya_slutsker@hotmail.com wrote:

> I am having error trying to run some mysql script (back from 2001). It
> gives me the error like :

> ERROR 1067 (42000) at line 48: Invalid default value for 'log_id'

> in the statement :

> CREATE TABLE Log (
> log_id int(11) DEFAULT '0' NOT NULL auto_increment,
> source_id smallint(6) DEFAULT '0' NOT NULL,
> l_time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
> l_nanos int(11) DEFAULT '0' NOT NULL,
> l_message text,
> PRIMARY KEY (log_id)
> );

> What is this? I am not familiar with mysql, the script is part of
> another installation script for the program made in 2001 by the
> company that no longer exists. We needed to upgrade the machine the
> program was running (redhat 8) to the RedHat ES 4. Now the installation
> script gives us this error.

> Please help.


log_id, l_nanos are INT datatypes.. should be ... DEFAULT 0 ....
note no quotes!!!

Re: Error in CREATE TABLE

wrote in message
news:1142609339.130712.264160@j33g2000cwa.googlegroups.com.. .
> I am having error trying to run some mysql script (back from 2001). It
> gives me the error like :
>
> ERROR 1067 (42000) at line 48: Invalid default value for 'log_id'
>
> in the statement :
>
> CREATE TABLE Log (
> log_id int(11) DEFAULT '0' NOT NULL auto_increment,
> source_id smallint(6) DEFAULT '0' NOT NULL,
> l_time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
> l_nanos int(11) DEFAULT '0' NOT NULL,
> l_message text,
> PRIMARY KEY (log_id)
> );
>
> What is this? I am not familiar with mysql, the script is part of
> another installation script for the program made in 2001 by the
> company that no longer exists. We needed to upgrade the machine the
> program was running (redhat 8) to the RedHat ES 4. Now the installation
> script gives us this error.
>
> Please help.

okay! I should try before I reply. Mea culpa!

When I do, I notice that you are declaring a default value of '0' for the
auto_increment field [log_id]. No can do in MySQL 4.1x . Come to think of
it, what does a default value in an auto_increment field mean anyway?
Remove "DEFUALT '0' " from the log_id line and it works fine.

BTW - I would still check for more text out at line 48 and beyond.
Thomas Bartkus

Re: Error in CREATE TABLE

That was line #48 cut out of text.

Re: Error in CREATE TABLE

"noone" wrote in message
news:983616c3fca53a4aa16652058c1f5879$1@www.firstdbasource.c om...
>
> log_id, l_nanos are INT datatypes.. should be ... DEFAULT 0 ....
> note no quotes!!!

True enough !
But
MySQL is quite promiscuous about doing automatic type conversions. INT
needs a number so MySQL just converts the string '0' to a numeric zero with
no complaint.

I hate that but it works.
Thomas Bartkus

Re: Error in CREATE TABLE

Yes if I remove "default '0'" It runs without giving me errors.
Do you mean in older implementations of mysql 'auto_increment' and
'default 0' would work and now it is an error?
I did not write that database, and people who did are not reachable.
So I wonder if removing that default I set some values wrong.

I am sorry for talking about things I do not understand fully, but we
need to have the program running, and I do not know where to ask.

Thanks again.

Re: Error in CREATE TABLE

Thomas Bartkus wrote:

> "noone" wrote in message
> news:983616c3fca53a4aa16652058c1f5879$1@www.firstdbasource.c om...
>>
>> log_id, l_nanos are INT datatypes.. should be ... DEFAULT 0 ....
>> note no quotes!!!

> True enough !
> But
> MySQL is quite promiscuous about doing automatic type conversions. INT
> needs a number so MySQL just converts the string '0' to a numeric zero with
> no complaint.

> I hate that but it works.
> Thomas Bartkus


If you intend on being a programmer for real systems, do not rely on one
vendors "automatic" translations. Program it correctly to start with and
you will save yourself a lot of grief! I even try to avoid (where
possible) functions that are unique to a particular vendor. And yes -
even though MySQL is "open source" it is still a vendor.

Re: Error in CREATE TABLE

wrote in message
news:1142613302.987088.229450@i39g2000cwa.googlegroups.com.. .
> Yes if I remove "default '0'" It runs without giving me errors.
> Do you mean in older implementations of mysql 'auto_increment' and
> 'default 0' would work and now it is an error?

It's possible but no, I don't meant that because I wouldn't know if it would
work in an older version. I am, however, saying that "default '0' " is
illogical in that context and consider it a flaw if any version MySQL failed
to complain about it.

BUT - that sort of thing does happen!
I only *know* that my version 4.1.13 didn't like it!

> I did not write that database, and people who did are not reachable.
> So I wonder if removing that default I set some values wrong.

Not likely!

Having an auto_increment field means you are asking the database to always
create a new positive integer (incremented by one) as the default. How can
you simultaneously have a zero default? It's a contradiction.

Further -

It's declared as PRIMARY KEY which further requires that no 2 records can
have the same value! Only 1 record could possible have a value of zero so
how can you have zero as the default. It's nonsensical no matter what angle
you look at it from.

I would just take it out and not look back!
Thomas Bartkus

> I am sorry for talking about things I do not understand fully, but we
> need to have the program running, and I do not know where to ask.
>
> Thanks again.

Re: Error in CREATE TABLE

ilya_slutsker@hotmail.com wrote:

> I am having error trying to run some mysql script (back from 2001). It
> gives me the error like :

> ERROR 1067 (42000) at line 48: Invalid default value for 'log_id'

> in the statement :

> CREATE TABLE Log (
> log_id int(11) DEFAULT '0' NOT NULL auto_increment,
> source_id smallint(6) DEFAULT '0' NOT NULL,
> l_time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
> l_nanos int(11) DEFAULT '0' NOT NULL,
> l_message text,
> PRIMARY KEY (log_id)
> );

> What is this? I am not familiar with mysql, the script is part of
> another installation script for the program made in 2001 by the
> company that no longer exists. We needed to upgrade the machine the
> program was running (redhat 8) to the RedHat ES 4. Now the installation
> script gives us this error.

> Please help.

AHHHHHHH!!!! Duh! Log is a reserved word. change the tablename to mylog
or logentries or something other than Log.

Re: Error in CREATE TABLE

"noone" wrote in message
news:d569073d4bf6d108e8da769f0a478261$1@www.firstdbasource.c om...
> Thomas Bartkus wrote:
>
> > "noone" wrote in message
> > news:983616c3fca53a4aa16652058c1f5879$1@www.firstdbasource.c om...
> >>
> >> log_id, l_nanos are INT datatypes.. should be ... DEFAULT 0 ....
> >> note no quotes!!!
>
> > True enough !
> > But
> > MySQL is quite promiscuous about doing automatic type conversions. INT
> > needs a number so MySQL just converts the string '0' to a numeric zero
with
> > no complaint.
>
> > I hate that but it works.
> > Thomas Bartkus
>
>
> If you intend on being a programmer for real systems, do not rely on one
> vendors "automatic" translations. Program it correctly to start with and
> you will save yourself a lot of grief! I even try to avoid (where
> possible) functions that are unique to a particular vendor. And yes -
> even though MySQL is "open source" it is still a vendor.

Congratulations! You are a pedant programmer after my own heart.
Did you fail to notice that I called MySQL "promiscuous" about type
conversion?

Your advise is excellent but do please note that most of us have to deal
with *rap written by other less diligent programmers. The top posters
dilemma being a perfect example!

Programmer for real systems
Thomas Bartkus

Re: Error in CREATE TABLE

"Thomas Bartkus" wrote in message
news:qPadnWm1I8ppdIfZRVn-tw@telcove.net...
> wrote in message
> news:1142613302.987088.229450@i39g2000cwa.googlegroups.com.. .
> Having an auto_increment field means you are asking the database to always
> create a new positive integer (incremented by one) as the default. How
> can
> you simultaneously have a zero default? It's a contradiction.

In fact, there was a bug on this issue: http://bugs.mysql.com/bug.php?id=157
No version numbers were identified in that bug log, but the bug was logged
March 17 2003 and fixed March 22 2003.

We can figure out what MySQL versions were available in that month (see
http://dev.mysql.com/doc/refman/4.1/en/news.html).

MySQL 3.23.56 was released March 13 2003
MySQL 4.0.12 was released March 15 2003 (the first production release of
4.0.x)
MySQL 4.1.0 (alpha) was still 1 month away

So I would guess that the bug was present in 3.23.56 and 4.0.12, and was
fixed in 4.0.13, and in all 4.1.x releases.

Regards,
Bill K.

Re: Error in CREATE TABLE

"Bill Karwin" wrote in message
news:dvesk90d1l@enews1.newsguy.com...
> "Thomas Bartkus" wrote in message
> news:qPadnWm1I8ppdIfZRVn-tw@telcove.net...
> > wrote in message
> > news:1142613302.987088.229450@i39g2000cwa.googlegroups.com.. .
> > Having an auto_increment field means you are asking the database to
always
> > create a new positive integer (incremented by one) as the default. How
> > can
> > you simultaneously have a zero default? It's a contradiction.
>
> In fact, there was a bug on this issue:
http://bugs.mysql.com/bug.php?id=157
> No version numbers were identified in that bug log, but the bug was logged
> March 17 2003 and fixed March 22 2003.
>
> We can figure out what MySQL versions were available in that month (see
> http://dev.mysql.com/doc/refman/4.1/en/news.html).
>
> MySQL 3.23.56 was released March 13 2003
> MySQL 4.0.12 was released March 15 2003 (the first production release of
> 4.0.x)
> MySQL 4.1.0 (alpha) was still 1 month away
>
> So I would guess that the bug was present in 3.23.56 and 4.0.12, and was
> fixed in 4.0.13, and in all 4.1.x releases.
>
> Regards,
> Bill K.

The very first 3 lines of the bug report tells it all -

Description:
MySQL allows defining a DEFAULT value to the auto_increment field, but
silently
drops it. "

There *was* a bug in MySQL that permitted the meaningless DEFAULT
And
When it managed to get past the interpreter it was silently ignored anyway.

That means one may fearlessly remove the offending DEFAULT '0' thereby
making the syntax more acceptable to more current versions of MySQL.

Thank you Bill

Thomas Bartkus