Problem In MYSQL with visual verify cde.sql

[CraftySue]

Hello,

I am using osCommerce 2.2 Milestone 2 version Update 051113 and am hoping that some knowledgeable forum member could help me with the following:

 

I am attempting to create the database table for osCommerce contrib Visual Verify Code2.

 

When I run the following SQL script:

# visual_verify_code.sql,v 1.0

#

# Written by William L. Peer, Jr. ([email protected])

#

# For use with:

# 1. osCommerce2.2m2, Open Source E-Commerce Solutions, http://www.oscommerce.com

# 2. visual_verify_code contribution was built for http://www.onlyvotives.com

#

#

# Released under the GNU General Public License

#

 

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 'CURRENTDATE()',

PRIMARY KEY (oscsid)

);

 

I get this 1067 Error (screenshot):

Error #1067- invalid default value for 'dt'

 

 

My question:

What would be correct default value for 'dt'

Please keep in mind I am new to MYSQL and have looked for the answer online

but can't seemed to find out how to correct.

 

My web hosting company (Webmasters.com) is using:

Database: MySQL 5.0.18-Debian_1

HTTP Server: Apache/1.3.33 Ben-SSL/1.55 (Unix) mod_gzip/1.3.26.1a mod_throttle/3.1.2 Chili!Soft-ASP/3.6.2 FrontPage/5.0.2.2635 mod_perl/1.29 PHP/4.3.11

PHP Version: 4.3.11 (Zend: 1.3.0)

MySQL 5.0.18-Debian_1 running on 63.webmasters.com as crftysue_os@localhost

 

Any help with this greatly appreciated!

Edited February 21, 2006 by CraftySue

[kgt]

To my knowledge, CURRENTDATE(), has never been a MySQL function. Even if it were, I don't think it ever would have worked with quotes around the function name. (However, the SQL you posted differs from that shown in the error you posted. Which one is correct?) I don't believe this is valid Postgre syntax either, but I may be wrong on that one.

 

You should be able to run this correctly by changing the query to the following:

 

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 CURRENT_TIMESTAMP,
PRIMARY KEY (oscsid)
);

[CraftySue]

Thanks for your response kgt

I used the query you provided and everything worked fine. :thumbsup:

The query I was using was the sql file provided in the contribution V V C2 (Visual Verify Code 2)

I see in the screenshot I provided however that I did leave off the following line:

 

DROP TABLE IF EXISTS visual_verify_code;

 

I guess just an error of omission. :blush:

Simple mistakes like that usually do occur (for me anyway) after working long hours. :blink:

 

Thanks for your help kgt!!!

Appreciate it!

[kgt]

The DROP TABLE statement would only be necessary if the table already existed. Since you're just now installing this and it looks like the CREATE TABLE statement never ran, that statement should have been unnecessary.

 

I was actually saying that the SQL you posted:

 

dt TIMESTAMP(12) NOT NULL DEFAULT 'CURRENTDATE()',

 

Does not match the clause in the error from the screenshot:

 

dt TIMESTAMP(12) NOT NULL DEFAULT 'CURDATE()',

 

Nevertheless, the error pertained to that clause. Had the missing DROP TABLE been the problem, you would have gotten a message like

 

#1050 - Table 'visual_verify_code' already exists

[CraftySue]

The DROP TABLE statement would only be necessary if the table already existed. Since you're just now installing this and it looks like the CREATE TABLE statement never ran, that statement should have been unnecessary.

 

I was actually saying that the SQL you posted:

 

dt TIMESTAMP(12) NOT NULL DEFAULT 'CURRENTDATE()',

 

Does not match the clause in the error from the screenshot:

 

dt TIMESTAMP(12) NOT NULL DEFAULT 'CURDATE()',

 

Nevertheless, the error pertained to that clause. Had the missing DROP TABLE been the problem, you would have gotten a message like

 

#1050 - Table 'visual_verify_code' already exists

 

Thanks for your comments,

appreciate the input. All new for me...

Time for a nap I guess or at least a break from the computer.

Edited February 21, 2006 by CraftySue

[The Criteria]

Hello all. I am receiving the 1067 error and this post seems to be the fix. Can someone walk me through this so I know how the change the query. I have no idea as to where to begin?

Edited May 10, 2006 by The Criteria