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.

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

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

Try

"SELECT `port`, behavior FROM ip_addresses"

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

Lee

On Feb 9, 2:09 pm, Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
> On 9 Feb 2007 09:52:52 -0800, "yawnmoth" <terra1...@yahoo.com> wrote:
>
> >When I do "SELECT port, behavior FROM ip_addresses", I get an error
> >1054: Unknown column 'port' in 'field list'.
>
> Try
>
> "SELECT `port`, behavior FROM ip_addresses"
>
> and see if that works - if so, MySQL is treating "port" as a "reserved
> word".
>
> Lee

Just tried it. Still get the same error :(

On Fri, 09 Feb 2007 21:44:37 +0100, yawnmoth <terra1024@yahoo.com> wrote:

> On Feb 9, 2:09 pm, Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
>> On 9 Feb 2007 09:52:52 -0800, "yawnmoth" <terra1...@yahoo.com> wrote:
>>
>> >When I do "SELECT port, behavior FROM ip_addresses", I get an error
>> >1054: Unknown column 'port' in 'field list'.
>>
>> Try
>>
>> "SELECT `port`, behavior FROM ip_addresses"
>>
>> and see if that works - if so, MySQL is treating "port" as a "reserved
>> word".
>>
>> Lee
>
> Just tried it. Still get the same error :(
>

What does a SHOW CREATE TABLE `ip_adresses` show?

--
Rik Wasmus

On Feb 9, 3:04 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
> <snip>
> What does a SHOW CREATE TABLE `ip_adresses` show?

[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

On Sat, 10 Feb 2007 02:18:52 +0100, yawnmoth <terra1024@yahoo.com> wrote:

> On Feb 9, 3:04 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
>> <snip>
>> What does a SHOW CREATE TABLE `ip_adresses` show?
>
> [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',

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

On Sat, 10 Feb 2007 02:41:12 +0100, Rik <luiheidsgoeroe@hotmail.com> wrote:

> On Sat, 10 Feb 2007 02:18:52 +0100, yawnmoth <terra1024@yahoo.com> wrote:
>
>> On Feb 9, 3:04 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
>>> <snip>
>>> What does a SHOW CREATE TABLE `ip_adresses` show?
>>
>> [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',
>
> Did you spot the space (or maybe it's another whitespace character)?
>
> SELECT `port ` FROM `phpbb_proxies`
>
> Another fine reason to always use backticks :)

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

On Feb 9, 7:55 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
> On Sat, 10 Feb 2007 02:41:12 +0100, Rik <luiheidsgoe...@hotmail.com> wrote:
> > On Sat, 10 Feb 2007 02:18:52 +0100,yawnmoth<terra1...@yahoo.com> wrote:
>
> >> On Feb 9, 3:04 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
> >>> <snip>
> >>> What does a SHOW CREATE TABLE `ip_adresses` show?
>
> >> [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',
>
> > Did you spot the space (or maybe it's another whitespace character)?

Nope. Nice catch! :D

>
> > SELECT `port ` FROM `phpbb_proxies`
>
> > Another fine reason to always use backticks :)
>
> Hmmz, I'm actually curious how this came to be. My MySQL 5.0 won't let me
> create it.

I was using MySQL 4.0.18.

Anyway, thanks! :)