Page 53 Sql Script

[eonfloyde]

I am really enjoying your books.  They focus on may concerns for todays systems.  Especially security.  Thanks for taking the time to write them.  

 

I finished PHP and MySQL for dynamic web sites.  I found great information throughout that title.  I have now started this book (effortless eCommerce 2nd ed.), which is very intriguing.

 

I started the book, and ran into a strange issue.  The very first SQL script failed on 'users' table.  The exception I received;

 

 

16:24:57 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 Error Code: 1293. Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause 0.000 sec

 

I figured I did something wrong, so I tried again this time using the downloaded script, and exact same error occurred.  In addition, the errata seems to be missing from the website, if there is an errata?  

 

I searched the forum to find that not one person has seen this issue.  I am wondering if my MySQL is malfunctioning...  I can not determine where the problem lies.  Any help wold be much appreciated. 

 

Please advise...

 

Thank you for your time,

-Mike-

 

[eonfloyde]

Sorry,

 

I forgot to list  my versions:

 

PHP 5.5.14

 

MySQL 5.5.38

 

OSX 10.9.4

 

In addition this is all I could find on the subject.  Can't say I completely understand if this will hurt the way the database will function for future chapters. 

 

 

MySQL auto initialises TIMESTAMP Columns with DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, so your first column TIME has the CURRENT_TIMESTAMP Added as default. Therefore by the time you explicitly add a DEFAULT to a column one already exists. You either need to change the order your columns are defined:

CREATE TABLE `silas`.`cs3_ds1` (
`ID` INT NOT NULL ,
`INSERT_TIME` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
`TIME` TIMESTAMP NOT NULL ,
`USER` VARCHAR(45) NOT NULL ,
`TIME1` TIMESTAMP NOT NULL ,
`TIME2` TIMESTAMP NOT NULL ,
PRIMARY KEY (`ID`) )
ENGINE = InnoDB

Or add defaults to your other timestamp columns:

CREATE TABLE `silas`.`cs3_ds1` (
`ID` INT NOT NULL ,
`TIME` TIMESTAMP NOT NULL DEFAULT 0,
`USER` VARCHAR(45) NOT NULL DEFAULT 0,
`TIME1` TIMESTAMP NOT NULL DEFAULT 0 ,
`TIME2` TIMESTAMP NOT NULL DEFAULT 0 ,
`INSERT_TIME` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`ID`) )
ENGINE = InnoDB

 

FOUND AT: http://stackoverflow.com/questions/11031946/getting-only-one-timestamp-column-with-current-timestamp-in-default-or-on-updat

[Larry]

Thanks for the nice words on the books. I really appreciate it. TIMESTAMP behavior in MySQL has changed repeatedly in MySQL, which is what you're seeing here. Your solution seems fine; thanks for sharing it!