Error inserting record, last possible index value already used

0

I'm having an error trying to insert a record someone knows how to solve.

Here is the error message:

Operation failed: There was an error while applying the SQL script to the database.
Executing:
INSERT INTO 'local'.'tab_evento' ('id') VALUES ('18446744073709551616');

ERROR 1264: 1264: Out of range value for column 'id' at row 1
SQL Statement:
INSERT INTO 'local'.'tab_evento' ('id') VALUES ('18446744073709551616')

Edit : The id is a BigInt (11) unsigned type, I've already tried to change it to unsigned (20) the same error.

    
asked by anonymous 03.07.2015 / 20:26

2 answers

0

The value you are entering is greater than the maximum value supported by the defined column type.

You can check the maximum sizes for each field: integer-types.

An alternative to your problem is to change the field type to varchar and set it to a larger size, but you will have to change the auto-increment >.

    
03.07.2015 / 20:31
0

In order to solve this problem it will be necessary to use a composite primary key, and to control the self-increment through a trigger, so it will no longer be automatic in the field itself.

Based on the script below, you can extend the rules of changing the periods according to your need and thus adjust the id to 1 each new period, but see that the period count is manual, to make it automatic it will be necessary extend the trigger code to be based on a third parameter table.

-- --------------------------------------------------------

--
-- Table structure for table 'tab_confere_evento'
--

CREATE TABLE IF NOT EXISTS 'tab_confere_evento' (
'id' bigint(11) NOT NULL,
  'evento_id' bigint(11) unsigned DEFAULT NULL,
  'evento_periodo' int(10) unsigned NOT NULL,
  'usuario' varchar(100) DEFAULT NULL,
  'status' int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table 'tab_evento'
--

CREATE TABLE IF NOT EXISTS 'tab_evento' (
'id' bigint(20) unsigned NOT NULL,
  'periodo' int(10) unsigned NOT NULL,
  'data' timestamp NULL DEFAULT NULL,
  'evento' varchar(120) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Tabela para guardar os logs de modulos abertos pelo usuário' ;

--
-- Triggers 'tab_evento'
--
DELIMITER //
CREATE TRIGGER 'prx_periodo_trigger' BEFORE INSERT ON 'tab_evento'
 FOR EACH ROW begin
    set new.id=(select ifnull((select max(prx_id)+1 from tab_event where periodo=new.periodo),1));
end
//
DELIMITER ;

--
-- Indexes for table 'tab_confere_evento'
--
ALTER TABLE 'tab_confere_evento'
 ADD PRIMARY KEY ('id','evento_periodo'), ADD KEY 'idx_evento_id' ('evento_id','evento_periodo');

--
-- Indexes for table 'tab_evento'
--
ALTER TABLE 'tab_evento'
 ADD PRIMARY KEY ('id','periodo'), ADD KEY 'id' ('id'), ADD KEY 'periodo' ('periodo');
    
03.07.2015 / 23:04