Sql Issue

[olaoyesunday]

Larry,
on page 53 and 54 of the book the mysql query you wrote orders, users tables do not execute :
The one for users table normally complain that #1293 – Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause in line 11.
2) For oders table I usually gets : #1005 – Can’t create table ‘ecommerce1.orders’ (errno: 150) (Details…).

I tried using the sql I downloaded with the book but  get what i described . please, let some one help me.

 

[Larry]

For the first issue, that is just a matter of what version of MySQL you're using. More recent versions support it. But you can drop the default CURRENT_TIMESTAMP value for the update. 

 

For the second issue, we'd need to see the detailed error message. 

[olaoyesunday]

Larry,

 Thanks very much. I'm using MySQL 5.5.27. When I dropped the default CURRENT_TIMESTAMP value for the update on users table it works and orders table also ran successfully.

 

Yours,

Sunday

[phpstuff]

I'm actually on the same pages 53-54 getting similar errors, so figured I'd add to this same post - updating my MySQl version may fix the error aboce, but I'm getting a wierd error below.  This happened when I copied / pasted (ctrl-v) into terminal...I didn't even hit "enter" and it looks like it started trying to run the code.

mysql> CREATE TABLE `pages` (
    -> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `categories_id` SMALLINT UNSIGNED NOT NULL,
    -> `title` VARCHAR(100) NOT NULL,
    -> `description` TINYTEXT NOT NULL,
    -> `content` LONGTEXT NULL,
    -> `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -> PRIMARY KEY (`id`),
    -> INDEX `date_created` (`date_created` ASC),
    -> INDEX `fk_pages_categories_idx` (`categories_id` ASC),
    -> CONSTRAINT `fk_pages_categories`
    ->
Display all 749 possibilities? (y or n)
    -> FOREIGN KEY (`categories_id`)
    ->
Display all 749 possibilities? (y or n)
    -> REFERENCES `categories` (`id`)
    ->
Display all 749 possibilities? (y or n)
    -> ON DELETE NO ACTION
    ->
Display all 749 possibilities? (y or n)
    -> ON UPDATE NO ACTION
    -> ) ENGINE = InnoDB DEFAULT CHARSET=utf8;

[Larry]

That's very strange. I've never seen that before.

[phpstuff]

I'm glad I now know that it shouldn't do what it's doing above...  I'll probably delete the database and start from scratch.  Thank you for your help Larry!

[phpstuff]

Odd, when I copy/paste the first part of the code I get normal results:

CREATE TABLE `categories` (
    `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `category` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `category_UNIQUE` (`category` ASC)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

This 2nd block of code starts having the issue where it asks for a Y/N confirmation:

CREATE TABLE `pages` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `categories_id` SMALLINT UNSIGNED NOT NULL,
    `title` VARCHAR(100) NOT NULL,
    `description` TINYTEXT NOT NULL,
    `content` LONGTEXT NULL,
    `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `date_created` (`date_created` ASC),
    INDEX `fk_pages_categories_idx` (`categories_id` ASC),
    CONSTRAINT `fk_pages_categories`
        FOREIGN KEY (`categories_id`)
        REFERENCES `categories` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

mysql> CREATE TABLE `pages` (
    -> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `categories_id` SMALLINT UNSIGNED NOT NULL,
    -> `title` VARCHAR(100) NOT NULL,
    -> `description` TINYTEXT NOT NULL,
    -> `content` LONGTEXT NULL,
    -> `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -> PRIMARY KEY (`id`),
    -> INDEX `date_created` (`date_created` ASC),
    -> INDEX `fk_pages_categories_idx` (`categories_id` ASC),
    -> CONSTRAINT `fk_pages_categories`
    ->
Display all 766 possibilities? (y or n)
    -> FOREIGN KEY (`categories_id`)
    ->
Display all 766 possibilities? (y or n)
    -> REFERENCES `categories` (`id`)
    ->
Display all 766 possibilities? (y or n)
    -> ON DELETE NO ACTION
    ->
Display all 766 possibilities? (y or n)
    -> ON UPDATE NO ACTION
    -> ) ENGINE = InnoDB DEFAULT CHARSET=utf8;
 

 

I did a google search and found:

 

http://www.redhat.com/archives/rhl-list/2005-April/msg03120.html

 

Not sure if it has anything to do with what I'm experiencing...  I'm on a Mac so maybe??

[Larry]

I guess that's theoretically possible, but I use a Mac and never had this. I wonder if it's the tabs? Try removing those and run it again.

[daisukexriku]

Hello! Im a newbie here. And I experienced the same issue while importing the SQL file. 

I read your solution but I still don't understand which line of code specifically am i deleting/changing? 

If you would be kind enough to show me I would really appreciate it.

 

Thanks in advance.

 

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;

[Larry]

Sorry, but could you be more specific? What's the exact problem you're having? Also, what MySQL version are you using and on what OS?