Bug #32266 error on insert with value 0x001d2l124578 + 0
Submitted: 11 Nov 2007 20:43 Modified: 18 Nov 2007 20:28
Reporter: Larry Hedrick Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.22 OS:Linux (ubunu lts)
Assigned to: CPU Architecture:Any
Tags: BIGINT, insert

[11 Nov 2007 20:43] Larry Hedrick
Description:
Tried looking to see if this was reported already but, had no success.  New to the mysql bug reporting so my searching ability still needs to evolve.  My apologies in advance if this is duplicate which I should have found.

I have a table with a bigint column.  When I try to insert a hex string an error is returned.  The bigint is a newtwork MAC address.  Mac address are 48 bit long so I wish to convert them to decimal and store them in a bigint.  A linux bash test script follow.

The error is:ERROR 1054 (42S22) at line 19: Unknown column '0x001d2l124578' in 'field list'

my bash script follows:
--------- start script ----------------------------
# MYSQL running on UBUNTU 
# from uname -a
#Linux rmd 2.6.15-26-server #1 SMP Fri Sep 8 21:00:37 UTC 2006 i686 GNU/Linux

# from mysqld --versoin
#mysqld  Ver 5.0.22-Debian_0ubuntu6.06.5-log for pc-linux-gnu on i486 (Debian Etch distribution)

mysql -t -h localhost -u fred -pabc<<END
use test;
DROP TABLE IF EXISTS mac;

# The following is an ethernet MAC address in hex and decimal
#   HEX 001D21124578
#   DEC 125108897144
#the next 2 lines work
select 0x001d21124578 + 0 AS 48Bit_Decimal_MAC_address;
select HEX(125108897144) AS 48BIT_HEX_MAC_address;

CREATE TABLE mac(
  mac   	  bigint       UNSIGNED UNIQUE NOT NULL
);
#next line works
insert into mac ( mac) values (0x7ffffffff + 0);

# the next line returns the following error
#ERROR 1054 (42S22) at line 19: Unknown column '0x001d2l124578' in 'field list'

insert into mac ( mac) values (0x001d2l124578 + 0);

exit
END
 ---------- script end --------------

The script output follows:
--------------- start script output ----------------------
+---------------------------+
| 48Bit_Decimal_MAC_address            |
+---------------------------+
|              125108897144                      | 
+---------------------------+
+----------------------+
| 48BIT_HEX_MAC_address          |
+----------------------+
| 1D21124578                           | 
+----------------------+
ERROR 1054 (42S22) at line 20: Unknown column '0x001d2l124578' in 'field list'
--------------- end script output -------------------

Hope this is not my mistake,  If it is sorry to have wasted you time.

Thanks.

How to repeat:
Run the follow script
------------------# MYSQL running on UBUNTU 
# from uname -a
#Linux rmd 2.6.15-26-server #1 SMP Fri Sep 8 21:00:37 UTC 2006 i686 GNU/Linux

# from mysqld --versoin
#mysqld  Ver 5.0.22-Debian_0ubuntu6.06.5-log for pc-linux-gnu on i486 (Debian Etch distribution)

mysql -t -h localhost -u fred -pabc<<END
use test;
DROP TABLE IF EXISTS mac;

# The following is an ethernet MAC address in hex and decimal
#   HEX 001D21124578
#   DEC 125108897144
#the next 2 lines work
select 0x001d21124578 + 0 AS 48Bit_Decimal_MAC_address;
select HEX(125108897144) AS 48BIT_HEX_MAC_address;

CREATE TABLE mac(
  mac   	  bigint       UNSIGNED UNIQUE NOT NULL
);
#next line works
insert into mac ( mac) values (0x7ffffffff + 0);

# the next line returns the following error
#ERROR 1054 (42S22) at line 19: Unknown column '0x001d2l124578' in 'field list'

insert into mac ( mac) values (0x001d2l124578 + 0);

exit
END
-----------------------
[11 Nov 2007 21:51] Larry Hedrick
User error
[18 Nov 2007 20:28] Larry Hedrick
After more testing, this was a user error and I am closing this bug report.