Why is MySql viewing an INT typed column as a LONG?

Question

(MySQL Version 5.6.36)

We have many tables in our database that have columns typed as INT. I was just running a linq query against the resultant DataTable from our currency table... and was implicitly casting the id field as int.

A "Specified cast is not valid" exception happened.

So I decided to run a standard foreach on the datarows, getting the type of the id column... sure enough it was being returned as an INT64.

This led me further down into the rabbit hole... I opened up a console, and started up the mysql command line, with the option --column-type-info turned on... and did a simple query.

mysql> select id from currency;
Field   1:  `id`
Catalog:    `def`
Database:   `intranet`
Table:      `currency`
Org_table:  `currency`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY


+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

This is the Describe:

+----------+------------+------+-----+---------+----------------+
| Field    | Type       | Null | Key | Default | Extra          |
+----------+------------+------+-----+---------+----------------+
| id       | int(11)    | NO   | PRI | NULL    | auto_increment |
| currency | varchar(3) | YES  |     | NULL    |                |
| per_usd  | double     | YES  |     | NULL    |                |
+----------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

And the Create:

CREATE TABLE `currency` (  `id` int(11) NOT NULL AUTO_INCREMENT,
  `currency` varchar(3) DEFAULT NULL,
  `per_usd` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

But this isn't limited to just this one table... all tables are showing INT, but returning LONG.

I've changed the id column to a MEDIUMINT in my testing, and instead of a LONG, it then returns an INT24.

Just wonder if there is something simple I am missing?

this might be helpful [https://stackoverflow.com/questions/5634104/what-is-the-size-of-column-of-int11-in-mysql-in-bytes] — Rohit Kumar, Jun 02 '17 at 15:43
Thank you, but I had already read through that... nothing that I could see that was explaining this phenomenon. — matthyde, Jun 02 '17 at 16:17
I am not too sure the issue is with the console response, as on MysqlWork bench and SQL Yog the datatype doesn't change. — Rohit Kumar, Jun 02 '17 at 17:09
Using the stock console application that comes with the mysql server - mysql — matthyde, Jun 07 '17 at 17:18
if possible can you please share the full screen (screenshot) of the console you are trying to use. — Rohit Kumar, Jun 07 '17 at 17:48
[Here is a fullscreen screenshot of the console](http://imgur.com/X2MMewC) — matthyde, Jun 07 '17 at 19:01

hans-könig · Answer 1 · 2017-06-03T08:51:06.537

0

Just delete that table and create a new one which you add unsigned to the id definition

CREATE TABLE  currency (

id int(11) unsigned NOT NULL AUTU_INCREMENT,

// the other definitions 

);

Hope you get the point. Sorry I am writing from my phone.

edited Jun 03 '17 at 08:51

answered Jun 02 '17 at 15:19

hans-könig

553
8
10

Unsigned or not, it still returns the same: `Type: LONG` – matthyde Jun 02 '17 at 15:32
I've read your question again and found that all tables are returning LONG. I have done some reading and found that LONG data type is an ORACLE thing. I have even checked my MYSQL database and there is nothing there as LONG data type. I was trying to say that may be your database is ORACLE but your code is showing MYSQL. I'm just baffled. – hans-könig Jun 03 '17 at 08:44

Why is MySql viewing an INT typed column as a LONG?

1 Answers1