Professional Web Applications Themes

easyphp & mysql5 - MySQL

Hi all, i need help with this query, on mysql5: ALTER TABLE `mreserv` ADD FOREIGN KEY ( `Acode` ) REFERENCES `Aclient` it returns errno: 1005 -------------------------------------------------------------------------------- error: Ne peut créer la table '.\test\#sql-a64_59.frm' (Errcode: 150) (translat.: can't create table '...) -------------------------------------------------------------------------------- mysql version: 5.0.18...

  1. #1

    Default easyphp & mysql5

    Hi all, i need help with this query, on mysql5:

    ALTER TABLE `mreserv` ADD FOREIGN KEY ( `Acode` ) REFERENCES `Aclient`


    it returns

    errno: 1005
    --------------------------------------------------------------------------------
    error: Ne peut créer la table '.\test\#sql-a64_59.frm' (Errcode: 150)
    (translat.: can't create table '...)
    --------------------------------------------------------------------------------
    mysql version: 5.0.18
    alfred Wallace Guest

  2. #2

    Default Re: easyphp & mysql5

    You have to specify to which table you'd like to refer to, like so:

    ALTER TABLE [table_name] ADD FOREIGN KEY ([column_name]) REFERENCES
    [referenced_table_name] ([referenced_column_name])

    Markus


    Markus Popp Guest

  3. #3

    Default Re: easyphp & mysql5


    "Markus Popp" <mfpgmx.li> wrote in message
    news:43b85b52$0$16890$91cee783newsreader01.highwa y.telekom.at...
    > You have to specify to which table you'd like to refer to, like so:
    >
    > ALTER TABLE [table_name] ADD FOREIGN KEY ([column_name]) REFERENCES
    > [referenced_table_name] ([referenced_column_name])
    >
    > Markus
    >
    >
    every time I try to add a foreign key to the table after a table's already
    been created, I get an error message "MySQL error 1005. can't create table
    '.\dbo\#sql-358_8.frm' (errno: 150)"
    any clues? bug?

    ALTER TABLE `dbo`.`quiz_reports_quiz_questions` ADD CONSTRAINT
    `FK_quiz_reports_quiz_questions_1` FOREIGN KEY
    `FK_quiz_reports_quiz_questions_1` (`quiz_id`) REFERENCES `quiz_quizzes`
    (`quiz_id`);

    MySQL Error Number 1005
    Can't create table '.`dbo\#sql-358_1.frm' (errno: 150)


    Jim Michaels Guest

  4. #4

    Default Re: easyphp & mysql5

    "Jim Michaels" <NOSPAMFORjmichae3> wrote:
    >
    > every time I try to add a foreign key to the table after a table's already
    > been created, I get an error message "MySQL error 1005. can't create table
    > '.\dbo\#sql-358_8.frm' (errno: 150)"
    > any clues? bug?
    RTFM

    Error 150 references to a situation where you create a foreign key
    contraint without having suitable indexes on the respective columns.
    Please note that you need those indexes in both tables. MySQL does
    not create indexes automatically (as other RDBMS do).


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  5. #5

    Default Re: easyphp & mysql5

    "Axel Schwenke" <axel.schwenkegmx.de> wrote in message
    news:6et5vd.ulq.lnidefix.xl.local...
    > "Jim Michaels" <NOSPAMFORjmichae3> wrote:
    >>
    >> every time I try to add a foreign key to the table after a table's
    >> already
    >> been created, I get an error message "MySQL error 1005. can't create
    >> table
    >> '.\dbo\#sql-358_8.frm' (errno: 150)"
    >> any clues? bug?
    >
    > RTFM
    > Error 150 references to a situation where you create a foreign key
    > contraint without having suitable indexes on the respective columns.
    Axel,

    Can you supply a link to the relevant part of the doentation that
    explains this as the cause for that errno 150? Before you tell users to
    RTFM, you should be sure that the FM has the answer.

    I found
    [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]
    which says only that "if an ALTER TABLE fails and it refers to errno 150,
    that means a foreign key definition would be incorrectly formed for the
    altered table." That's close to the explanation you mentioned, but it's
    unclear and suggests no remedy.

    I found [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html[/url] which
    has a similar statement, but no further help.

    Also not especially helpful:

    C:> perror 150
    MySQL error code 150: Foreign key constraint is incorrectly formed
    > Please note that you need those indexes in both tables. MySQL does
    > not create indexes automatically (as other RDBMS do).
    RTFM yourself Axel! ;-)

    [url]http://dev.mysql.com/doc/refman/4.1/en/innodb-news-4-1-2.html[/url] says:
    "Changes in MySQL/InnoDB-4.1.2
    .. . .
    Automatically create a suitable index on a FOREIGN KEY, if the user does not
    create one. Removes most of the cases of Error 1005 (errno 150) in table
    creation."

    The OP said he's using MySQL 5.0.18, so this change in 4.1.2 should still
    apply.

    And
    [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]
    says:
    "In the referencing table, there must be an index where the foreign key
    columns are listed as the first columns in the same order. Such an index is
    created on the referencing table automatically if it does not exist."

    Finally, you must admit that the error message of "can't create table" is
    nearly useless. Any error message should ideally tell the user the
    following:

    1. What happened (e.g. "can't create table")
    2. Why it happened. ("referencing field(s) lacking index")
    3. What to do to fix it. ("create index manually before creating foreign key
    constraint")

    Regards,
    Bill K.


    Bill Karwin Guest

  6. #6

    Default Re: easyphp & mysql5

    "Jim Michaels" <NOSPAMFORjmichae3> wrote in message
    news:T9idnS_-9qHJpYvZRVn-tAcomcast.com...
    > every time I try to add a foreign key to the table after a table's already
    > been created, I get an error message "MySQL error 1005. can't create table
    > '.\dbo\#sql-358_8.frm' (errno: 150)"
    > any clues? bug?
    Several bugs appear with this errno symptom.
    [url]http://bugs.mysql.com/bug.php?id=8625[/url]
    [url]http://bugs.mysql.com/bug.php?id=10082[/url]
    [url]http://bugs.mysql.com/bug.php?id=10325[/url]
    [url]http://bugs.mysql.com/bug.php?id=15324[/url]

    The usual suggestion is to use SHOW INNODB STATUS for more information.

    MySQL versions prior to 4.1.2 do not create an index automatically when you
    create a foreign key constraint. You need to create the index manually and
    then create the constraint. Later versions of MySQL should create an index
    on the referencing field(s) implicitly when you create the foreign key
    constraint.

    Note that the referenced column (e.g. quiz_quizzes.quiz_id) must be indexed
    to be a valid referenced field. Also, if the referencing and referenced
    fields are string fields, they must have identical lengths, and compatible
    character sets and collations (it's best if they're both BINARY).

    Regards,
    Bill K.


    Bill Karwin Guest

  7. #7

    Default Re: easyphp & mysql5

    "Bill Karwin" <billkarwin.com> wrote:
    > "Axel Schwenke" <axel.schwenkegmx.de> wrote in message
    >>
    >> RTFM
    >> Error 150 references to a situation where you create a foreign key
    >> contraint without having suitable indexes on the respective columns.
    >
    > Can you supply a link to the relevant part of the doentation that
    > explains this as the cause for that errno 150? Before you tell users to
    > RTFM, you should be sure that the FM has the answer.
    >
    > I found
    > [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]
    > which says only that "if an ALTER TABLE fails and it refers to errno 150,
    > that means a foreign key definition would be incorrectly formed for the
    > altered table." That's close to the explanation you mentioned, but it's
    > unclear and suggests no remedy.
    Bill,

    you stopped quoting the manual just before the most important sentence:

    "Starting from MySQL 4.0.13, you can use SHOW INNODB STATUS to display
    a detailed explanation of the latest InnoDB foreign key error in the
    server."


    Example:

    mysql> alter table t2 add constraint foreign key (c1) references t1 (c1);
    ERROR 1005 (HY000): Can't create table './test/#sql-2856_1.frm' (errno: 150)
    mysql> show innodb status;
    ....
    ------------------------
    LATEST FOREIGN KEY ERROR
    ------------------------
    060315 9:27:44 Error in foreign key constraint of table test/#sql-2856_1:
    foreign key (c1) references t1 (c1):
    Cannot find an index in the referenced table where the
    referenced columns appear as the first columns, or column types
    in the table and the referenced table do not match for constraint.
    >> Please note that you need those indexes in both tables. MySQL does
    >> not create indexes automatically (as other RDBMS do).
    >
    > RTFM yourself Axel! ;-)
    Got me :-)

    I correct myself: "InnoDB does not create an index in the referenced
    table automatically."
    > Finally, you must admit that the error message of "can't create table" is
    > nearly useless. Any error message should ideally tell the user the
    > following:
    >
    > 1. What happened (e.g. "can't create table")
    > 2. Why it happened. ("referencing field(s) lacking index")
    > 3. What to do to fix it. ("create index manually before creating foreign key
    > constraint")
    Well, the problem here is, that maintaining foreign key constraints
    is delegated to the storage engine (and it's no-ops in all SE except
    InnoDB). Furthermore, ALTER TABLE is implemented as CREATE (new) TABLE,
    INSERT INTO (new) SELECT FROM (old), RENAME TABLE (new) TO (old).
    Therefor you get an error for CREATE TABLE although you did ALTER TABLE

    After all the best advise to recover from error 150 is to use SHOW
    INNODB STATUS, because all the "interesting" things are happening
    inside InnoDB. In praxis error 150 almost every time refers to a
    missing index. Probably the manual should contain a note on it.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  8. #8

    Default Re: easyphp & mysql5

    "Axel Schwenke" <axel.schwenkegmx.de> wrote in message
    news:1nk8vd.idu.lnidefix.xl.local...
    > you stopped quoting the manual just before the most important sentence:
    >
    > "Starting from MySQL 4.0.13, you can use SHOW INNODB STATUS to display
    > a detailed explanation of the latest InnoDB foreign key error in the
    > server."
    Excellent! That's good advice for folks using InnoDB.
    > Well, the problem here is, that maintaining foreign key constraints
    > is delegated to the storage engine (and it's no-ops in all SE except
    > InnoDB).
    Hmm. Then the pluggable SE architecture should provide some way of
    delegating error message generation. That is, the SE should be able to
    "bubble up" an error message and report an error seamlessly through the
    standard MySQL error reporting. Requiring the use of a SE-specific
    statement to get full error details makes it non-obvious for someone writing
    a client application to get appropriate error details, unless they are also
    the database architect. They at least have to know what storage engine is
    being used for a given table, which may be a detail outside the knowledge of
    an application developer. Also, access to the errors may be further
    complicated by using client interfaces, e.g. Hibernate.

    Anyway, thanks for the clarifications.

    Regards,
    Bill K.


    Bill Karwin Guest

Similar Threads

  1. #39642 [NEW]: Compile with mysql5.0.27max error
    By bbisky at sohu dot com in forum PHP Bugs
    Replies: 1
    Last Post: November 27th, 07:22 AM
  2. Problems after installing EasyPHP 1.7
    By davids58@gmail.com in forum MySQL
    Replies: 6
    Last Post: April 4th, 06:30 PM
  3. easy gen2 and easyphp
    By Paul Watt in forum MySQL
    Replies: 1
    Last Post: November 13th, 10:10 PM
  4. Testing Server with EasyPHP
    By stevebrz in forum Dreamweaver AppDev
    Replies: 4
    Last Post: May 3rd, 08:05 AM
  5. EASYpHP 1.7 INSTALLATION
    By Québec in forum PHP Development
    Replies: 2
    Last Post: December 4th, 10:29 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139