Uploaded image for project: 'phpBB'
  1. phpBB
  2. PHPBB-16245

Overflow in MSSQL attempting to manage attachments when collection > 2.1 GB

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Fixed
    • Icon: Major Major
    • 3.2.10-RC1, 3.3.1-RC1
    • 3.2.7, 3.2.8
    • Attachments
    • PHP 7.2.24, MSSQL 2012 SP4

      When Admin/Posting/Manage Attachments is clicked, and there are attachments whose collective size exceeds 2.1 GB, it fails with this exception:

      General Error

      SQL ERROR [ mssqlnative ]

      SQLSTATE: 22003 code: 8115 message: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Arithmetic overflow error converting expression to data type int. [8115]

      SQL

      SELECT COUNT(a.attach_id) AS num_files, SUM(a.filesize) AS upload_dir_size FROM phpbb_attachments a WHERE a.is_orphan = 0

      BACKTRACE

      FILE: (not given by php)
      LINE: (not given by php)
      CALL: msg_handler()

      FILE: [ROOT]/phpbb/db/driver/driver.php
      LINE: 997
      CALL: trigger_error()

      FILE: [ROOT]/phpbb/db/driver/mssqlnative.php
      LINE: 147
      CALL: phpbb\db\driver\driver->sql_error()

      FILE: [ROOT]/phpbb/db/driver/factory.php
      LINE: 329
      CALL: phpbb\db\driver\mssqlnative->sql_query()

      FILE: [ROOT]/includes/acp/acp_attachments.php
      LINE: 1298
      CALL: phpbb\db\driver\factory->sql_query()

      FILE: [ROOT]/includes/acp/acp_attachments.php
      LINE: 1331
      CALL: acp_attachments->get_attachment_stats()

      FILE: [ROOT]/includes/acp/acp_attachments.php
      LINE: 1133
      CALL: acp_attachments->check_stats_accuracy()

      FILE: [ROOT]/includes/functions_module.php
      LINE: 676
      CALL: acp_attachments->main()

      FILE: [ROOT]/adm/index.php
      LINE: 82
      CALL: p_master->load_active()
       
       
      The problem is that phpbb_attachments.filesize is an INT, with a maximum upper limit of 2,147,483,647. When attempting to SUM the filesizes column, MSSQL expects the result to also be an INT. The sum of those values exceeds the INT value, and results in an overflow.
       
      Instead of this SQL:

      SELECT COUNT(a.attach_id) AS num_files, SUM(a.filesize) AS upload_dir_size FROM phpbb_attachments a WHERE a.is_orphan = 0

      Using this SQL would fix this problem:
       
      SELECT COUNT(a.attach_id) AS num_files, SUM(CAST(a.filesize AS BIGINT)) AS upload_dir_size FROM phpbb_attachments a WHERE a.is_orphan = 0
       
      This is in MSSQL - other SQL servers may have different limits on their INT values.

            Marc Marc
            gsmaclean gsmaclean
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: