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.

 

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. 

Link to post
Share on other sites
  • 2 weeks later...

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

Link to post
Share on other sites
  • 4 months later...

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;
Link to post
Share on other sites
  • 2 weeks later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

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