Error entering record, last possible Intel value already used

Asked

Viewed 277 times

-2

I’m having an error trying to enter a record someone knows how to solve.

Follow 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 type Bigint(11) unsigned, I’ve tried to change it to bigint(20) unsigned and continues with the same mistake.

mysql

  • For optimization reasons, wouldn’t it be interesting to use a composite ID? by what it seems this table works with events, you would have other fields that could compose this primary key and make it composed? something like date?

  • @Delfino even using the date as part of the primary key the problem continues, of the error, is that the id field does not increment more of the same error

  • actually you will have to change the bank and go to use a precedent to auto-increment the composite key, zeroing the sequential counter whenever it pops, and have a second field that will count this burst either by period or simply by overflow, I think this can help you; http://stackoverflow.com/a/18120156/2766598

  • @Delfino this case of creating process to auto-increment does not solve because the incremented number I have, it just can not insert it because it is larger than the field supports, if I reset the counter I get problems in the relationship between another table

  • seriously that you have 18446744073709551616 records in your table??? I was impressed. what is this about?

2 answers

0

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

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

An alternative to your problem, is to change the field type to sweep and set to a larger size, but will have to change the way in which the auto-increment.

  • no, my last entry on the table is 18446744073709551615

  • he is a BIGINT

  • Even if bigint unsigned is still smaller than the value you try to insert. bigint usigned -> (2*(2 63))-1

  • I already know that the maximum value supported in Bigint is https://dev.mysql.com/doc/refman/5.5/en/integer-types.html but what now? I do what, locked everything here

  • It would have an example of source code of how would be the auto-increment this case a pk being varchar and how would be the relationships that already exist in this table have to maintain?

  • The auto-increment control would have to be done by code using transaction (every good framework already takes care of it for you), how much fk you would have to refactor the bank. Well, as a first solution I see this, wait to see if someone comes up with a better idea.

  • has a framework that manages Mysql auto-increment? Which?

Show 2 more comments

0

To solve such a problem it will be necessary to use a composite primary key, and control the auto increment through a Trigger, so it will no longer be automatic in the field itself.

Based on the script below, you will be able to extend the rules for changing periods as you need and so adjust the id to 1 for each new period, but note that the period count is manual, to make it automatic it will be necessary to extend the Rigger code so that it is based on a third parameterization 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`);

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.