Jump to content
Larry Ullman's Book Forums

Possible Error In Sql.Sql For 2Nd Edition Of This Book


Recommended Posts

I successfully downloaded the code for the 2nd edition of this book. But when I tried to import the .sql into MAMP I got the following error:

 

SQL query:

CREATE TABLE `users` (

`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`type` ENUM( 'member', 'admin' ) NOT NULL DEFAULT 'member',
`username` VARCHAR( 45 ) NOT NULL ,
`email` VARCHAR( 80 ) NOT NULL ,
`pass` VARCHAR( 255 ) NOT NULL ,
`first_name` VARCHAR( 45 ) NOT NULL ,
`last_name` VARCHAR( 45 ) NOT NULL ,
`date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`date_expires` DATE NOT NULL ,
`date_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY ( `id` ) ,
UNIQUE INDEX `username_UNIQUE` ( `username` ASC ) ,
UNIQUE INDEX `email_UNIQUE` ( `email` ASC ) ,
INDEX `login` ( `email` ASC , `pass` ASC )

) ENGINE = InnoDB DEFAULT CHARSET = utf8;



MySQL said: dot.gif

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

 

 

 

The 3 tables that came before this error; categories, pages, pdfs were created correctly. I'm not sure how to fix this.

 

 

Link to comment
Share on other sites

Remove the

NOT NULL DEFAULT CURRENT_TIMESTAMP

from your date_modified column. MySQL doesn't allow two columns to current_timestamp on inserts for some weird reason. The insert will thus result null, while it'll update the timestamp on update queries.

 

I don't know if this will break anything in the application, but that'll fix your import errors at least.

Link to comment
Share on other sites

I looked up the sql.sql code (example 1) in the e-commerce 1st edition book and am assuming that it should be the same for 2nd edition. Here is the corrected code that creates the date_modified column:

	`date_modified` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',

So, the download has an error in it.

 

Larry, to let you know:

 

The following link downloads both Example 1 and Example 2 of 2nd edition but with an error in line 50 of Example 1 sql.sql:

`date_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE

This link downloads only Example 1 scripts of 1st edition. Example 2 code is missing:

http://www.larryullman.com/books/effortless-e-commerce-with-php-and-mysql-2nd-edition/

effortless_ecommerce_2nd.zip (721 KB, last modified 11/17/2013)
Link to comment
Share on other sites

  • 3 weeks later...

I just downloaded the 2nd edition files today (December 23, 2013) and the problem remains at line 50 of the sql.sql file.

 

As chop discovered replacing line 50 with:

`date_modified` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',

Seems to work (at least the query is good and the table is created). I can't say for sure if it affects the application since I haven't gotten to that point yet.

Link to comment
Share on other sites

  • 5 years later...
On 12/6/2013 at 3:03 PM, Larry said:

Thanks. I'm working on posting updated scripts.

downloaded your SQL script for the second site, was typing the create categories table when i got this same error date modified . tried copy and pasting your script but it still returned an error . Otherwise I found your book immensely awesome and helpful

Link to comment
Share on other sites

 Share

×
×
  • Create New...