André Fecteau André Fecteau - 4 months ago 10
MySQL Question

Error 1264 MySQL even though the value is within int parameters

I have read and understand that int can only hold a max value of 2147483647. My issue is that the field MySQL is saying it has a problem with, has the value of "1", which is well within 2147483647.

Here is the response I am getting:

Error Code: 1264. Out of range value for column 'state_Title_ID' at row 1


Here is the code I am using:

INSERT INTO tbl_position_history
SELECT * FROM tbl_active_position
WHERE position_AI_Number=1
AND uconn_position_ID=99002805;


Here is the information it is selecting:

position_AI_Number, uconn_Position_ID, data_Start_Date, data_End_Date, employee_AI_Number, state_Title_ID, functional_Title_ID, org_ID, pos_Paid_By_ID, bond_Fund_Paid_Engineer, union_ID, job_Start_Date, full_Time_Employee, additional_History, status_TSHC, object_ID, cust_Floater, assignment_ID, status_ID, staffing_Plan_ID, shift_ID, cust_Area_ID
'1', '99002805', '0000-00-00', '2016-07-27', '2', '1', '1', '1616', '2', '1', '6', NULL, '1', 'Facilities IT', '0', '5240', '0', NULL, '2', NULL, '1', NULL


This is the position history table:

Field, Type, Null, Key, Default, Extra
'position_AI_Number', 'int(11)', 'NO', 'PRI', NULL, ''
'uconn_Position_ID', 'int(11)', 'NO', 'PRI', NULL, ''
'data_Start_Date', 'date', 'NO', '', NULL, ''
'state_Title_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'functional_Title_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'org_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'pos_Paid_By_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'bond_Fund_Paid_Engineer', 'int(11)', 'YES', 'MUL', NULL, ''
'union_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'employee_AI_Number', 'int(11)', 'YES', 'MUL', NULL, ''
'data_End_Date', 'date', 'YES', '', NULL, ''
'job_Start_Date', 'date', 'YES', '', NULL, ''
'full_Time_Employee', 'float', 'YES', '', NULL, ''
'additional_History', 'longtext', 'YES', '', NULL, ''
'status_TSHC', 'tinyint(1)', 'YES', '', NULL, ''
'object_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'cust_Floater', 'tinyint(1)', 'YES', '', NULL, ''
'assignment_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'status_ID', 'int(11)', 'NO', 'MUL', NULL, ''
'staffing_Plan_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'shift_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'cust_Area_ID', 'int(11)', 'YES', 'MUL', NULL, ''


This is the position active history:

Field, Type, Null, Key, Default, Extra
'position_AI_Number', 'int(11)', 'NO', 'PRI', NULL, 'auto_increment'
'uconn_Position_ID', 'int(11)', 'NO', 'PRI', NULL, ''
'data_Start_Date', 'date', 'NO', '', NULL, ''
'data_End_Date', 'date', 'YES', '', NULL, ''
'employee_AI_Number', 'int(11)', 'YES', 'MUL', NULL, ''
'state_Title_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'functional_Title_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'org_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'pos_Paid_By_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'bond_Fund_Paid_Engineer', 'int(11)', 'YES', 'MUL', NULL, ''
'union_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'job_Start_Date', 'date', 'YES', '', NULL, ''
'full_Time_Employee', 'float', 'YES', '', NULL, ''
'additional_History', 'longtext', 'YES', '', NULL, ''
'status_TSHC', 'tinyint(1)', 'YES', '', NULL, ''
'object_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'cust_Floater', 'tinyint(1)', 'YES', '', NULL, ''
'assignment_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'status_ID', 'int(11)', 'NO', 'MUL', NULL, ''
'staffing_Plan_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'shift_ID', 'int(11)', 'YES', 'MUL', NULL, ''
'cust_Area_ID', 'int(11)', 'YES', 'MUL', NULL, ''


This code runs perfectly even though it has the same value for State_Title_ID:

INSERT INTO `positioncontrolv1`.`tbl_position_history`
(`position_AI_Number`,
`uconn_Position_ID`,
`data_Start_Date`,
`status_ID`,
`state_title_ID`)
VALUES
(100, 1010101, '2016-08-02',1,1);


Any suggestions on what to do here?

Answer

You are inserting tbl_active_position.data_End_Date (a date) into tbl_position_history.state_Title_ID (an int). MySQL will not automagically match up your field names.