MySQL Problem with large numbersfind and insert row to another table using mysql triggercan this mysql query...

Creature in Shazam mid-credits scene?

Aragorn's "guise" in the Orthanc Stone

If a character has darkvision, can they see through an area of nonmagical darkness filled with lightly obscuring gas?

Request info on 12/48v PSU

A social experiment. What is the worst that can happen?

Why is so much work done on numerical verification of the Riemann Hypothesis?

Problem with TransformedDistribution

What if a revenant (monster) gains fire resistance?

Redundant comparison & "if" before assignment

How to implement a feedback to keep the DC gain at zero for this conceptual passive filter?

The screen of my macbook suddenly broken down how can I do to recover

Can someone explain how this makes sense electrically?

Is this toilet slogan correct usage of the English language?

I am looking for the correct translation of love for the phrase "in this sign love"

Can I sign legal documents with a smiley face?

Is a bound state a stationary state?

Not using 's' for he/she/it

Does the expansion of the universe explain why the universe doesn't collapse?

How could a planet have erratic days?

Argument list too long when zipping large list of certain files in a folder

Which one is correct as adjective “protruding” or “protruded”?

Why electric field inside a cavity of a non-conducting sphere not zero?

The IT department bottlenecks progress. How should I handle this?

What are the purposes of autoencoders?



MySQL Problem with large numbers


find and insert row to another table using mysql triggercan this mysql query be optimized further?MySQL: Why queries with subqueries are much faster than single query?Allow multiple users to see procedures and functionsOptimize speed on 400GB MyISAM tableIf I query records matching some value, why does InnoDB examine most of the records that had that value once, but have changed since then?How can I use MySQL variables in subqueries?max allowed packet keep changingError 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES)select MAX() from MySQL view (2x INNER JOIN) is slow













0















I'm trying to put together a function to convert a size from one type to another (e.g. bytes >> gigabytes).



An extract is below



DROP FUNCTION fc_convSize;
DELIMITER $$
CREATE FUNCTION `fc_convSize`(inVal BIGINT)
RETURNS DECIMAL(25,2)
NO SQL
BEGIN
-- bytes 2 exabytes
SET @inVal := inVal/1024/1024/1024/1024/1024/1024;
RETURN @outVal;
END$$
DELIMITER ;


But it doesn't work with large numbers. e.g.



mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT fc_convSize(@input1);
ERROR 1264 (22003): Out of range value for column 'inVal' at row 1


But from what I can tell, max_binlog_cache_size is 184467440737095*47520* and the maximum for a BIGINT is 184467440737095*51615* so it should fit, right?



But it gets stranger.



mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @input1/1024/1024/1024/1024/1024/1024;
+---------------------------------------+
| @input1/1024/1024/1024/1024/1024/1024 |
+---------------------------------------+
| -0.000000000000003552713679 |
+---------------------------------------+
1 row in set (0.00 sec)


mysql> SET @input2 := (18446744073709547520);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @input2/1024/1024/1024/1024/1024/1024;
+---------------------------------------+
| @input2/1024/1024/1024/1024/1024/1024 |
+---------------------------------------+
| -0.000000000000003552713679 |
+---------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT 18446744073709547520/1024/1024/1024/1024/1024/1024;
+----------------------------------------------------+
| 18446744073709547520/1024/1024/1024/1024/1024/1024 |
+----------------------------------------------------+
| 15.999999999999996447286321 |
+----------------------------------------------------+
1 row in set (0.00 sec)


What's going on???










share|improve this question














bumped to the homepage by Community 5 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.




















    0















    I'm trying to put together a function to convert a size from one type to another (e.g. bytes >> gigabytes).



    An extract is below



    DROP FUNCTION fc_convSize;
    DELIMITER $$
    CREATE FUNCTION `fc_convSize`(inVal BIGINT)
    RETURNS DECIMAL(25,2)
    NO SQL
    BEGIN
    -- bytes 2 exabytes
    SET @inVal := inVal/1024/1024/1024/1024/1024/1024;
    RETURN @outVal;
    END$$
    DELIMITER ;


    But it doesn't work with large numbers. e.g.



    mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT fc_convSize(@input1);
    ERROR 1264 (22003): Out of range value for column 'inVal' at row 1


    But from what I can tell, max_binlog_cache_size is 184467440737095*47520* and the maximum for a BIGINT is 184467440737095*51615* so it should fit, right?



    But it gets stranger.



    mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT @input1/1024/1024/1024/1024/1024/1024;
    +---------------------------------------+
    | @input1/1024/1024/1024/1024/1024/1024 |
    +---------------------------------------+
    | -0.000000000000003552713679 |
    +---------------------------------------+
    1 row in set (0.00 sec)


    mysql> SET @input2 := (18446744073709547520);
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT @input2/1024/1024/1024/1024/1024/1024;
    +---------------------------------------+
    | @input2/1024/1024/1024/1024/1024/1024 |
    +---------------------------------------+
    | -0.000000000000003552713679 |
    +---------------------------------------+
    1 row in set (0.00 sec)


    mysql> SELECT 18446744073709547520/1024/1024/1024/1024/1024/1024;
    +----------------------------------------------------+
    | 18446744073709547520/1024/1024/1024/1024/1024/1024 |
    +----------------------------------------------------+
    | 15.999999999999996447286321 |
    +----------------------------------------------------+
    1 row in set (0.00 sec)


    What's going on???










    share|improve this question














    bumped to the homepage by Community 5 mins ago


    This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.


















      0












      0








      0








      I'm trying to put together a function to convert a size from one type to another (e.g. bytes >> gigabytes).



      An extract is below



      DROP FUNCTION fc_convSize;
      DELIMITER $$
      CREATE FUNCTION `fc_convSize`(inVal BIGINT)
      RETURNS DECIMAL(25,2)
      NO SQL
      BEGIN
      -- bytes 2 exabytes
      SET @inVal := inVal/1024/1024/1024/1024/1024/1024;
      RETURN @outVal;
      END$$
      DELIMITER ;


      But it doesn't work with large numbers. e.g.



      mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
      Query OK, 0 rows affected (0.00 sec)

      mysql> SELECT fc_convSize(@input1);
      ERROR 1264 (22003): Out of range value for column 'inVal' at row 1


      But from what I can tell, max_binlog_cache_size is 184467440737095*47520* and the maximum for a BIGINT is 184467440737095*51615* so it should fit, right?



      But it gets stranger.



      mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
      Query OK, 0 rows affected (0.00 sec)

      mysql> SELECT @input1/1024/1024/1024/1024/1024/1024;
      +---------------------------------------+
      | @input1/1024/1024/1024/1024/1024/1024 |
      +---------------------------------------+
      | -0.000000000000003552713679 |
      +---------------------------------------+
      1 row in set (0.00 sec)


      mysql> SET @input2 := (18446744073709547520);
      Query OK, 0 rows affected (0.00 sec)

      mysql> SELECT @input2/1024/1024/1024/1024/1024/1024;
      +---------------------------------------+
      | @input2/1024/1024/1024/1024/1024/1024 |
      +---------------------------------------+
      | -0.000000000000003552713679 |
      +---------------------------------------+
      1 row in set (0.00 sec)


      mysql> SELECT 18446744073709547520/1024/1024/1024/1024/1024/1024;
      +----------------------------------------------------+
      | 18446744073709547520/1024/1024/1024/1024/1024/1024 |
      +----------------------------------------------------+
      | 15.999999999999996447286321 |
      +----------------------------------------------------+
      1 row in set (0.00 sec)


      What's going on???










      share|improve this question














      I'm trying to put together a function to convert a size from one type to another (e.g. bytes >> gigabytes).



      An extract is below



      DROP FUNCTION fc_convSize;
      DELIMITER $$
      CREATE FUNCTION `fc_convSize`(inVal BIGINT)
      RETURNS DECIMAL(25,2)
      NO SQL
      BEGIN
      -- bytes 2 exabytes
      SET @inVal := inVal/1024/1024/1024/1024/1024/1024;
      RETURN @outVal;
      END$$
      DELIMITER ;


      But it doesn't work with large numbers. e.g.



      mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
      Query OK, 0 rows affected (0.00 sec)

      mysql> SELECT fc_convSize(@input1);
      ERROR 1264 (22003): Out of range value for column 'inVal' at row 1


      But from what I can tell, max_binlog_cache_size is 184467440737095*47520* and the maximum for a BIGINT is 184467440737095*51615* so it should fit, right?



      But it gets stranger.



      mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
      Query OK, 0 rows affected (0.00 sec)

      mysql> SELECT @input1/1024/1024/1024/1024/1024/1024;
      +---------------------------------------+
      | @input1/1024/1024/1024/1024/1024/1024 |
      +---------------------------------------+
      | -0.000000000000003552713679 |
      +---------------------------------------+
      1 row in set (0.00 sec)


      mysql> SET @input2 := (18446744073709547520);
      Query OK, 0 rows affected (0.00 sec)

      mysql> SELECT @input2/1024/1024/1024/1024/1024/1024;
      +---------------------------------------+
      | @input2/1024/1024/1024/1024/1024/1024 |
      +---------------------------------------+
      | -0.000000000000003552713679 |
      +---------------------------------------+
      1 row in set (0.00 sec)


      mysql> SELECT 18446744073709547520/1024/1024/1024/1024/1024/1024;
      +----------------------------------------------------+
      | 18446744073709547520/1024/1024/1024/1024/1024/1024 |
      +----------------------------------------------------+
      | 15.999999999999996447286321 |
      +----------------------------------------------------+
      1 row in set (0.00 sec)


      What's going on???







      mysql






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Sep 2 '16 at 13:34









      IGGtIGGt

      9971027




      9971027





      bumped to the homepage by Community 5 mins ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







      bumped to the homepage by Community 5 mins ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
























          2 Answers
          2






          active

          oldest

          votes


















          0














          Do you have to declare the argument as a bigint?



          CREATE OR REPLACE FUNCTION fc_convSize(inVal decimal(40,0))
          RETURNS DECIMAL(25,2)
          NO SQL
          BEGIN
          return inVal/1024/1024/1024/1024/1024/1024;
          END$$

          select fc_convSize(18446744073709547520000000123333232323);
          +-----------------------------------------------------+
          | fc_convSize(18446744073709547520000000123333232323) |
          +-----------------------------------------------------+
          | 15999999999999996447.29 |
          +-----------------------------------------------------+
          1 row in set, 1 warning (0.00 sec)


          However:



          select fc_convSize(10000*18446744073709547520);
          ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(10000 * 18446744073709547520)'


          MySQL tries to fit the result of the multiplication in a bigint which fails. You can cast one of the arguments to void this:



          select fc_convSize(10000*cast(18446744073709547520 as decimal(40,0)));
          +----------------------------------------------------------------+
          | fc_convSize(10000*cast(18446744073709547520 as decimal(40,0))) |
          +----------------------------------------------------------------+
          | 160000.00 |
          +----------------------------------------------------------------+
          1 row in set, 1 warning (0.00 sec)


          Note that I just grabbed decimal(40,0) out of thin air, just pick something that is large enough for your needs.






          share|improve this answer

































            0














            Bytes to gigabytes on only 3 divides:



            /1024/1024/1024


            A simpler (and faster) way to do this



            SET @input1 := (SELECT @@global.max_binlog_cache_size);


            is



            SELECT @input1 := @@global.max_binlog_cache_size;


            The problem with SET @input2 := (18446744073709547520); is that the number is bigger than can fit in a BIGINT SIGNED. So it (somewhat erroneously) stores the 64 bits blindly; this turns into a negative number.



            Consider CAST:



            SELECT CAST(@@global.max_binlog_cache_size AS UNSIGNED)/1024/1024/1024;
            --> 17179869183.999996185303





            share|improve this answer























              Your Answer








              StackExchange.ready(function() {
              var channelOptions = {
              tags: "".split(" "),
              id: "182"
              };
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function() {
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled) {
              StackExchange.using("snippets", function() {
              createEditor();
              });
              }
              else {
              createEditor();
              }
              });

              function createEditor() {
              StackExchange.prepareEditor({
              heartbeatType: 'answer',
              autoActivateHeartbeat: false,
              convertImagesToLinks: false,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: null,
              bindNavPrevention: true,
              postfix: "",
              imageUploader: {
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              },
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              });


              }
              });














              draft saved

              draft discarded


















              StackExchange.ready(
              function () {
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f148669%2fmysql-problem-with-large-numbers%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              0














              Do you have to declare the argument as a bigint?



              CREATE OR REPLACE FUNCTION fc_convSize(inVal decimal(40,0))
              RETURNS DECIMAL(25,2)
              NO SQL
              BEGIN
              return inVal/1024/1024/1024/1024/1024/1024;
              END$$

              select fc_convSize(18446744073709547520000000123333232323);
              +-----------------------------------------------------+
              | fc_convSize(18446744073709547520000000123333232323) |
              +-----------------------------------------------------+
              | 15999999999999996447.29 |
              +-----------------------------------------------------+
              1 row in set, 1 warning (0.00 sec)


              However:



              select fc_convSize(10000*18446744073709547520);
              ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(10000 * 18446744073709547520)'


              MySQL tries to fit the result of the multiplication in a bigint which fails. You can cast one of the arguments to void this:



              select fc_convSize(10000*cast(18446744073709547520 as decimal(40,0)));
              +----------------------------------------------------------------+
              | fc_convSize(10000*cast(18446744073709547520 as decimal(40,0))) |
              +----------------------------------------------------------------+
              | 160000.00 |
              +----------------------------------------------------------------+
              1 row in set, 1 warning (0.00 sec)


              Note that I just grabbed decimal(40,0) out of thin air, just pick something that is large enough for your needs.






              share|improve this answer






























                0














                Do you have to declare the argument as a bigint?



                CREATE OR REPLACE FUNCTION fc_convSize(inVal decimal(40,0))
                RETURNS DECIMAL(25,2)
                NO SQL
                BEGIN
                return inVal/1024/1024/1024/1024/1024/1024;
                END$$

                select fc_convSize(18446744073709547520000000123333232323);
                +-----------------------------------------------------+
                | fc_convSize(18446744073709547520000000123333232323) |
                +-----------------------------------------------------+
                | 15999999999999996447.29 |
                +-----------------------------------------------------+
                1 row in set, 1 warning (0.00 sec)


                However:



                select fc_convSize(10000*18446744073709547520);
                ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(10000 * 18446744073709547520)'


                MySQL tries to fit the result of the multiplication in a bigint which fails. You can cast one of the arguments to void this:



                select fc_convSize(10000*cast(18446744073709547520 as decimal(40,0)));
                +----------------------------------------------------------------+
                | fc_convSize(10000*cast(18446744073709547520 as decimal(40,0))) |
                +----------------------------------------------------------------+
                | 160000.00 |
                +----------------------------------------------------------------+
                1 row in set, 1 warning (0.00 sec)


                Note that I just grabbed decimal(40,0) out of thin air, just pick something that is large enough for your needs.






                share|improve this answer




























                  0












                  0








                  0







                  Do you have to declare the argument as a bigint?



                  CREATE OR REPLACE FUNCTION fc_convSize(inVal decimal(40,0))
                  RETURNS DECIMAL(25,2)
                  NO SQL
                  BEGIN
                  return inVal/1024/1024/1024/1024/1024/1024;
                  END$$

                  select fc_convSize(18446744073709547520000000123333232323);
                  +-----------------------------------------------------+
                  | fc_convSize(18446744073709547520000000123333232323) |
                  +-----------------------------------------------------+
                  | 15999999999999996447.29 |
                  +-----------------------------------------------------+
                  1 row in set, 1 warning (0.00 sec)


                  However:



                  select fc_convSize(10000*18446744073709547520);
                  ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(10000 * 18446744073709547520)'


                  MySQL tries to fit the result of the multiplication in a bigint which fails. You can cast one of the arguments to void this:



                  select fc_convSize(10000*cast(18446744073709547520 as decimal(40,0)));
                  +----------------------------------------------------------------+
                  | fc_convSize(10000*cast(18446744073709547520 as decimal(40,0))) |
                  +----------------------------------------------------------------+
                  | 160000.00 |
                  +----------------------------------------------------------------+
                  1 row in set, 1 warning (0.00 sec)


                  Note that I just grabbed decimal(40,0) out of thin air, just pick something that is large enough for your needs.






                  share|improve this answer















                  Do you have to declare the argument as a bigint?



                  CREATE OR REPLACE FUNCTION fc_convSize(inVal decimal(40,0))
                  RETURNS DECIMAL(25,2)
                  NO SQL
                  BEGIN
                  return inVal/1024/1024/1024/1024/1024/1024;
                  END$$

                  select fc_convSize(18446744073709547520000000123333232323);
                  +-----------------------------------------------------+
                  | fc_convSize(18446744073709547520000000123333232323) |
                  +-----------------------------------------------------+
                  | 15999999999999996447.29 |
                  +-----------------------------------------------------+
                  1 row in set, 1 warning (0.00 sec)


                  However:



                  select fc_convSize(10000*18446744073709547520);
                  ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(10000 * 18446744073709547520)'


                  MySQL tries to fit the result of the multiplication in a bigint which fails. You can cast one of the arguments to void this:



                  select fc_convSize(10000*cast(18446744073709547520 as decimal(40,0)));
                  +----------------------------------------------------------------+
                  | fc_convSize(10000*cast(18446744073709547520 as decimal(40,0))) |
                  +----------------------------------------------------------------+
                  | 160000.00 |
                  +----------------------------------------------------------------+
                  1 row in set, 1 warning (0.00 sec)


                  Note that I just grabbed decimal(40,0) out of thin air, just pick something that is large enough for your needs.







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Sep 2 '16 at 15:00

























                  answered Sep 2 '16 at 14:47









                  LennartLennart

                  13.3k21243




                  13.3k21243

























                      0














                      Bytes to gigabytes on only 3 divides:



                      /1024/1024/1024


                      A simpler (and faster) way to do this



                      SET @input1 := (SELECT @@global.max_binlog_cache_size);


                      is



                      SELECT @input1 := @@global.max_binlog_cache_size;


                      The problem with SET @input2 := (18446744073709547520); is that the number is bigger than can fit in a BIGINT SIGNED. So it (somewhat erroneously) stores the 64 bits blindly; this turns into a negative number.



                      Consider CAST:



                      SELECT CAST(@@global.max_binlog_cache_size AS UNSIGNED)/1024/1024/1024;
                      --> 17179869183.999996185303





                      share|improve this answer




























                        0














                        Bytes to gigabytes on only 3 divides:



                        /1024/1024/1024


                        A simpler (and faster) way to do this



                        SET @input1 := (SELECT @@global.max_binlog_cache_size);


                        is



                        SELECT @input1 := @@global.max_binlog_cache_size;


                        The problem with SET @input2 := (18446744073709547520); is that the number is bigger than can fit in a BIGINT SIGNED. So it (somewhat erroneously) stores the 64 bits blindly; this turns into a negative number.



                        Consider CAST:



                        SELECT CAST(@@global.max_binlog_cache_size AS UNSIGNED)/1024/1024/1024;
                        --> 17179869183.999996185303





                        share|improve this answer


























                          0












                          0








                          0







                          Bytes to gigabytes on only 3 divides:



                          /1024/1024/1024


                          A simpler (and faster) way to do this



                          SET @input1 := (SELECT @@global.max_binlog_cache_size);


                          is



                          SELECT @input1 := @@global.max_binlog_cache_size;


                          The problem with SET @input2 := (18446744073709547520); is that the number is bigger than can fit in a BIGINT SIGNED. So it (somewhat erroneously) stores the 64 bits blindly; this turns into a negative number.



                          Consider CAST:



                          SELECT CAST(@@global.max_binlog_cache_size AS UNSIGNED)/1024/1024/1024;
                          --> 17179869183.999996185303





                          share|improve this answer













                          Bytes to gigabytes on only 3 divides:



                          /1024/1024/1024


                          A simpler (and faster) way to do this



                          SET @input1 := (SELECT @@global.max_binlog_cache_size);


                          is



                          SELECT @input1 := @@global.max_binlog_cache_size;


                          The problem with SET @input2 := (18446744073709547520); is that the number is bigger than can fit in a BIGINT SIGNED. So it (somewhat erroneously) stores the 64 bits blindly; this turns into a negative number.



                          Consider CAST:



                          SELECT CAST(@@global.max_binlog_cache_size AS UNSIGNED)/1024/1024/1024;
                          --> 17179869183.999996185303






                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Sep 3 '16 at 1:33









                          Rick JamesRick James

                          43.6k22259




                          43.6k22259






























                              draft saved

                              draft discarded




















































                              Thanks for contributing an answer to Database Administrators Stack Exchange!


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid



                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.


                              To learn more, see our tips on writing great answers.




                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f148669%2fmysql-problem-with-large-numbers%23new-answer', 'question_page');
                              }
                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              Anexo:Material bélico de la Fuerza Aérea de Chile Índice Aeronaves Defensa...

                              Always On Availability groups resolving state after failover - Remote harden of transaction...

                              update json value to null Announcing the arrival of Valued Associate #679: Cesar Manara ...