Professional Web Applications Themes

SELECT errors out saying a column doesn't exist that SHOW COLUMNS thinks does exist - MySQL

When I do "SELECT port, behavior FROM ip_addresses", I get an error 1054: Unknown column 'port' in 'field list'. Doing a "SHOW COLUMNS FROM ip_addresses", however, shows that I *do* in fact have a column 'port'. Here's the exact output I get: Array ( [Field] => ip_address [Type] => varchar(8) [Null] => [Key] => PRI [Default] => [Extra] => ) Array ( [Field] => behavior [Type] => tinyint(2) [Null] => [Key] => [Default] => 0 [Extra] => ) Array ( [Field] => port [Type] => varchar(4) [Null] => YES [Key] => [Default] => 0 [Extra] => ) Array ( [Field] ...

  1. #1

    Default SELECT errors out saying a column doesn't exist that SHOW COLUMNS thinks does exist

    When I do "SELECT port, behavior FROM ip_addresses", I get an error
    1054: Unknown column 'port' in 'field list'.

    Doing a "SHOW COLUMNS FROM ip_addresses", however, shows that I *do*
    in fact have a column 'port'. Here's the exact output I get:

    Array
    (
    [Field] => ip_address
    [Type] => varchar(8)
    [Null] =>
    [Key] => PRI
    [Default] =>
    [Extra] =>
    )

    Array
    (
    [Field] => behavior
    [Type] => tinyint(2)
    [Null] =>
    [Key] =>
    [Default] => 0
    [Extra] =>
    )

    Array
    (
    [Field] => port
    [Type] => varchar(4)
    [Null] => YES
    [Key] =>
    [Default] => 0
    [Extra] =>
    )

    Array
    (
    [Field] => last_checked
    [Type] => int(11)
    [Null] =>
    [Key] =>
    [Default] => 0
    [Extra] =>
    )

    So why is MySQL simultaneously telling me that 'port' is a column and
    isn't? And what can I do to fix it?

    If it matters, the MySQL server in question is running on
    ipowerweb.com.

    yawnmoth Guest

  2. #2

    Default Re: SELECT errors out saying a column doesn't exist that SHOW COLUMNS thinks does exist

    On 9 Feb 2007 09:52:52 -0800, "yawnmoth" <com> wrote:
     

    Try

    "SELECT `port`, behavior FROM ip_addresses"

    and see if that works - if so, MySQL is treating "port" as a "reserved
    word".

    Lee

    Lee Guest

  3. #3

    Default Re: SELECT errors out saying a column doesn't exist that SHOW COLUMNS thinks does exist

    On Feb 9, 2:09 pm, Lee Peedin <rr.com> wrote: 
    >
    > Try
    >
    > "SELECT `port`, behavior FROM ip_addresses"
    >
    > and see if that works - if so, MySQL is treating "port" as a "reserved
    > word".
    >
    > Lee[/ref]

    Just tried it. Still get the same error :(

    yawnmoth Guest

  4. #4

    Default Re: SELECT errors out saying a column doesn't exist that SHOW COLUMNS thinks does exist

    On Fri, 09 Feb 2007 21:44:37 +0100, yawnmoth <com> wrote:
     
    >>
    >> Try
    >>
    >> "SELECT `port`, behavior FROM ip_addresses"
    >>
    >> and see if that works - if so, MySQL is treating "port" as a "reserved
    >> word".
    >>
    >> Lee[/ref]
    >
    > Just tried it. Still get the same error :(
    >[/ref]

    What does a SHOW CREATE TABLE `ip_adresses` show?

    --
    Rik Wasmus
    Rik Guest

  5. #5

    Default Re: SELECT errors out saying a column doesn't exist that SHOW COLUMNS thinks does exist

    On Feb 9, 3:04 pm, Rik <com> wrote: 

    [Table] => phpbb_proxies
    [Create Table] => CREATE TABLE `ip_addresses` (
    `ip_address` varchar(8) NOT NULL default '',
    `behavior` tinyint(2) NOT NULL default '0',
    `port ` varchar(4) default '0',
    `last_checked` int(11) NOT NULL default '0',
    PRIMARY KEY (`ip_address`)
    ) TYPE=MyISAM

    yawnmoth Guest

  6. #6

    Default Re: SELECT errors out saying a column doesn't exist that SHOW COLUMNS thinks does exist

    On Sat, 10 Feb 2007 02:18:52 +0100, yawnmoth <com> wrote:
     
    >
    > [Table] => phpbb_proxies
    > [Create Table] => CREATE TABLE `ip_addresses` (
    > `ip_address` varchar(8) NOT NULL default '',
    > `behavior` tinyint(2) NOT NULL default '0',
    > `port ` varchar(4) default '0',[/ref]

    Did you spot the space (or maybe it's another whitespace character)?

    SELECT `port ` FROM `phpbb_proxies`

    Another fine reason to always use backticks :)
    --
    Rik Wasmus
    Rik Guest

  7. #7

    Default Re: SELECT errors out saying a column doesn't exist that SHOW COLUMNS thinks does exist

    On Sat, 10 Feb 2007 02:41:12 +0100, Rik <com> wrote:
     
    >>
    >> [Table] => phpbb_proxies
    >> [Create Table] => CREATE TABLE `ip_addresses` (
    >> `ip_address` varchar(8) NOT NULL default '',
    >> `behavior` tinyint(2) NOT NULL default '0',
    >> `port ` varchar(4) default '0',[/ref]
    >
    > Did you spot the space (or maybe it's another whitespace character)?
    >
    > SELECT `port ` FROM `phpbb_proxies`
    >
    > Another fine reason to always use backticks :)[/ref]

    Hmmz, I'm actually curious how this came to be. My MySQL 5.0 won't let me
    create it.

    If you're still having troubles selecting from it with a space I suspect
    it's some weird character. Easiest way to solve it:

    CREATE TABLE `ip_addresses_temp` (
    `ip_address` varchar(8) NOT NULL default '',
    `behavior` tinyint(2) NOT NULL default '0',
    `port` varchar(4) default '0',
    `last_checked` int(11) NOT NULL default '0',
    PRIMARY KEY (`ip_address`)
    ) TYPE=MyISAM

    INSERT INTO `ip_addresses_temp` SELECT * FROM `ip_addresses`

    DROP TABLE `ip_addresses`

    RENAME TABLE ip_addresses_temp TO ip_addresses

    --
    Rik Wasmus
    Rik Guest

  8. #8

    Default Re: SELECT errors out saying a column doesn't exist that SHOW COLUMNS thinks does exist

    On Feb 9, 7:55 pm, Rik <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]

    Nope. Nice catch! :D
     

    >
    > Hmmz, I'm actually curious how this came to be. My MySQL 5.0 won't let me
    > create it.[/ref]

    I was using MySQL 4.0.18.

    Anyway, thanks! :)

    yawnmoth Guest

Similar Threads

  1. Replies: 3
    Last Post: October 1st, 09:24 PM
  2. Replies: 6
    Last Post: July 15th, 01:22 AM
  3. Replies: 1
    Last Post: July 29th, 04:31 PM
  4. Does the column exist
    By Iain in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 14th, 03:48 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