Jump to content
Larry Ullman's Book Forums

Recommended Posts

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.

 

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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;

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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??

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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;

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...