mysql, BIGINT value is out of range

Question

I looked at some of the other answers for similar questions but none of them seemed to help me.

  [2020-01-28 23:26:29] CDbCommand failed to execute the SQL statement: SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT value is out of range in 'count(`Um3mpV1OJwy6m`.`jobsubmits`.`id`) * 500000 * 4398046511104'
[2020-01-29 00:26:47] CronjobController::actionRun 0
[2020-01-29 00:27:07] CDbException#1
(
    [errorInfo] => array
    (
        0 => '22003'
        1 => 1690
        2 => 'BIGINT value is out of range in \'count(`Um3mpV1OJwy6m`.`jobsubmits`.`id`) * 500000 * 4398046511104\''
    )
    [*:message] => 'CDbCommand failed to execute the SQL statement: SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT value is out of range in \'count(`Um3mpV1OJwy6m`.`jobsubmits`.`id`) * 500000 * 4398046511104\''
    [Exception:string] => ''
    [*:code] => 22003
    [*:file] => '/home/crypto-data/yiimp/site/web/framework/db/CDbCommand.php'
    [*:line] => 543
    [Exception:trace] => array

What not to understand? Your number is too large for `BIGINT`. Read: https://stackoverflow.com/questions/1632312/is-bigint8-the-largest-integer-mysql-can-store — PM 77-1, Jan 29 '20 at 00:01
bigint goes from -9223372036854775808 to 9223372036854775807, so it is limited — nbk, Jan 29 '20 at 00:02
Does this answer your question? [What to do when you need integers larger than 20 digits on mysql?](https://stackoverflow.com/questions/7142604/what-to-do-when-you-need-integers-larger-than-20-digits-on-mysql) — FanoFN, Jan 29 '20 at 05:45

Bill Karwin · Answer 1 · 2020-01-29T00:24:46.673

1

Your value is the COUNT() multiplied by:

500000 * 4398046511104 = 2199023255552000000

Max value of a signed BIGINT is 2⁶³-1 = 9223372036854775807

9223372036854775807 / 2199023255552000000 = 4.1943

So if your COUNT() is greater than 4, then it will exceed the max value that can be represented in a signed BIGINT.

edited Jan 29 '20 at 00:24

answered Jan 29 '20 at 00:16

Bill Karwin

538,548
86
673
828

mysql, BIGINT value is out of range

1 Answers1