What is wrong with this SQL?

[Roaddoctor]

I run on PHP 4.3.2, MySQL 3.23.58 OSC 2.2RC1

 

What is wrong with this SQL?

 

I get this error when I try to execute:

 

ERROR 1064 at line 15: You have an error in your SQL syntax near 'NOW(),

PRIMARY KEY (oscsid)

)' at line 4

 

DROP TABLE IF EXISTS visual_verify_code;
CREATE TABLE visual_verify_code (
 oscsid varchar(32) NOT NULL,
 code varchar(6) NOT NULL,
 dt TIMESTAMP(12) NOT NULL DEFAULT NOW(),
 PRIMARY KEY (oscsid)
);

 

 

Thank you for the any help !

[Jan Zonjee]

I run on PHP 4.3.2, MySQL 3.23.58 OSC 2.2RC1

 

What is wrong with this SQL?

 

I get this error when I try to execute:

 

ERROR 1064 at line 15: You have an error in your SQL syntax near 'NOW(),

PRIMARY KEY (oscsid)

)' at line 4

 

DROP TABLE IF EXISTS visual_verify_code;
CREATE TABLE visual_verify_code (
 oscsid varchar(32) NOT NULL,
 code varchar(6) NOT NULL,
 dt TIMESTAMP(12) NOT NULL DEFAULT NOW(),
 PRIMARY KEY (oscsid)
);

According to the MySQL manual for timestamp on old MySQL databases the column TIMESTAMP data type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. My guess is that MySQL doesn't want to add the date and time of the moment you create that table in that column because it goes against the intended use.

[Roaddoctor]

According to the MySQL manual for timestamp on old MySQL databases the column TIMESTAMP data type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. My guess is that MySQL doesn't want to add the date and time of the moment you create that table in that column because it goes against the intended use.

 

Hey Jan, long time... thanks for the reply!

With me having the mysql skils equivelant to a slug, I still dont have a clue what is wrong.

 

Your referenced link was helpful but not specific, and I searched out the NULL vs NOT NULL, with NOT NULL seeming correct for the table. So I am assuming the "DEFAULT NOW(), is the problem?

Would this table work correctly without the "DEFAULT NOW()," ?

I've also tried "DEFAULT CURRENT_TIMESTAMP" which gives the same error

 

 

DROP TABLE IF EXISTS visual_verify_code;
CREATE TABLE visual_verify_code (
 oscsid varchar(32) NOT NULL,
 code varchar(6) NOT NULL,
 dt TIMESTAMP(12) NOT NULL DEFAULT NOW(),
 PRIMARY KEY (oscsid)
);

 

Is this the probable solution?

 

DROP TABLE IF EXISTS visual_verify_code;
CREATE TABLE visual_verify_code (
 oscsid varchar(32) NOT NULL,
 code varchar(6) NOT NULL,
 dt TIMESTAMP(12) NOT NULL,
 PRIMARY KEY (oscsid)
);

 

any feedback much appreciated

[Jan Zonjee]

Is this the probable solution?

I think this will work also:

DROP TABLE IF EXISTS visual_verify_code;
CREATE TABLE visual_verify_code (
 oscsid varchar(32) NOT NULL,
 code varchar(6) NOT NULL,
 dt TIMESTAMP(12),
 PRIMARY KEY (oscsid)
);

From the MySQL documentation I understand that on an insert the field for timestamp is always populated, but you can also use NULL in the query to insert the timestamp. It will also be updated on an update (again I understand that from the docs, never used it myself).

From the docs I also understand that in MySQL 4 the timestamp has a length of 19 for year-month-day hour-minutes-seconds. Maybe something to think about :)

[Roaddoctor]

cool - thanks for the quick response!

 

From the contribution readme, the timestamp is aplied to each individual VVC code generated and later used for garbage collection, with that said, does that change which version you would select, yours? or the one I listed as probable?

 

btw - here is the contribution... sql seems to be a topic with several variations...

http://addons.oscommerce.com/info/1560

[Jan Zonjee]

From the contribution readme, the timestamp is aplied to each individual VVC code generated and later used for garbage collection, with that said, does that change which version you would select, yours? or the one I listed as probable?

Interestingly enough using your original sql does not trigger an error on MySQL5. The default is however not NOW() but CURRENT_TIMESTAMP.

 

If I "dump" that table it generates the following sql:

DROP TABLE IF EXISTS `visual_verify_code`;
CREATE TABLE `visual_verify_code` (
 `oscsid` varchar(32) NOT NULL,
 `code` varchar(6) NOT NULL,
 `dt` timestamp NOT NULL default CURRENT_TIMESTAMP,
 PRIMARY KEY  (`oscsid`)
)