Weird SQLServer errors when attaching files to a post

Get help with installation and running phpBB 3.1.x here. Please do not post bug reports, feature requests, or extension related questions here.
Scam Warning
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: phpBB 3.1.x is at its End of Life stage and support will NOT be provided after July 1st, 2018.
Locked
User avatar
MarkDHamill
Registered User
Posts: 3876
Joined: Fri Aug 02, 2002 12:36 am
Location: Florence, MA USA
Contact:

Weird SQLServer errors when attaching files to a post

Post by MarkDHamill » Wed Sep 30, 2015 9:05 pm

Client is using SQLServer database on an IIS web server. You can see the error that is occurring when they try to add a post with an image attached.
sqlservererror.png
The current value for the row in the phpbb_config table for "upload_dir_size" is 2,147,455,555 which is the number of bytes for all the files in the files folder. Add to its size and the error is triggered, presumably because it exceeds the maximum value of whatever data type it is going into. That part is not too clear. The SQLServer error is described here:

https://technet.microsoft.com/en-us/lib ... .105).aspx

My first attempt was to increase the size of the config_value column for the phpbb_config table from VARCHAR(255) to VARCHAR(8000), the maximum SQLServer allows. But that didn't work so I started to dig into the code a bit. I think the problem is line 191 in /phpbb/config/db.php:

Code: Select all

		$sql_update = $this->db->cast_expr_to_string($this->db->cast_expr_to_bigint('config_value') . ' + ' . (int) $increment);
For SQLServer there is no cast_expr_to_string or cast_expr_to_bigint functions to override the default for the class. I do see some in the driver for Postgres (/phpbb/config/postgres.php):

Code: Select all

	/**
	* {@inheritDoc}
	*/
	function cast_expr_to_bigint($expression)
	{
		return 'CAST(' . $expression . ' as DECIMAL(255, 0))';
	}

	/**
	* {@inheritDoc}
	*/
	function cast_expr_to_string($expression)
	{
		return 'CAST(' . $expression . ' as VARCHAR(255))';
	}
I'm thinking to get around the problem I need to copy some of this code into /phpbb/config/postgres.php /phpbb/config/mssqlnative.php and maybe change it a bit, something like this:

Code: Select all

	/**
	* {@inheritDoc}
	*/
	function cast_expr_to_bigint($expression)
	{
		return 'CAST(' . $expression . ' as BIGINT)';
	}
SQLServer does have a CAST function documented here:

https://msdn.microsoft.com/en-us/library/ms187928.aspx

Does this sound right?

This really sounds like a bug that needs to be fixed. I am happy to file it but I need to patch the correct program in the meantime so they can attach files to posts again. Does my solution sound right? Casting it to BIGINT should work because a BIGINT can contain values in this range on SQLServer:

-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

Was I right to increase the config_value column size to VARCHAR(8000)? Or is that not part of the problem?
Get the latest versions of my Digests and Smartfeed extensions.
Need phpBB services or a phpBB consultant? I offer most phpBB services.

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50668
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Weird SQLServer errors when attaching files to a post

Post by stevemaury » Wed Sep 30, 2015 9:35 pm

Why not just set the Total attachment quota value in the ACP, Attachment settings?
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

User avatar
MarkDHamill
Registered User
Posts: 3876
Joined: Fri Aug 02, 2002 12:36 am
Location: Florence, MA USA
Contact:

Re: Weird SQLServer errors when attaching files to a post

Post by MarkDHamill » Wed Sep 30, 2015 10:00 pm

The attachment quota is set to 0, so it's unlimited.
Get the latest versions of my Digests and Smartfeed extensions.
Need phpBB services or a phpBB consultant? I offer most phpBB services.

User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: Weird SQLServer errors when attaching files to a post

Post by Oyabun1 » Wed Sep 30, 2015 10:29 pm

MarkDHamill wrote:Was I right to increase the config_value column size to VARCHAR(8000)?
The config value only stores the setting for the attachment quota not the attachments themselves. A value of 100 exibytes would be stored as 115292150460685000000 (a 21 character number). So, 255 characters should be more than enough.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests

User avatar
MarkDHamill
Registered User
Posts: 3876
Joined: Fri Aug 02, 2002 12:36 am
Location: Florence, MA USA
Contact:

Re: Weird SQLServer errors when attaching files to a post

Post by MarkDHamill » Wed Sep 30, 2015 10:56 pm

That's what I thought. The number, however big, would be converted to a character and stored. So the likely issue is what is triggering the error. What code gets executed by the function cast_expr_to_bigint if the database driver has no such function? I've searched the phpBB code and the only thing I see is for Postgres. Presumably, phpBB won't execute that if SQLServer is the database.
Get the latest versions of my Digests and Smartfeed extensions.
Need phpBB services or a phpBB consultant? I offer most phpBB services.

User avatar
MarkDHamill
Registered User
Posts: 3876
Joined: Fri Aug 02, 2002 12:36 am
Location: Florence, MA USA
Contact:

Re: Weird SQLServer errors when attaching files to a post

Post by MarkDHamill » Wed Sep 30, 2015 11:15 pm

Oyabun1 wrote:
MarkDHamill wrote:Was I right to increase the config_value column size to VARCHAR(8000)?
The config value only stores the setting for the attachment quota not the attachments themselves. A value of 100 exibytes would be stored as 115292150460685000000 (a 21 character number). So, 255 characters should be more than enough.
The limit for the attachment quota is the configuration variable attachment_quota. I'm pretty sure upload_dir_size just tracks the current number of bytes for all the files in the files folder.
Get the latest versions of my Digests and Smartfeed extensions.
Need phpBB services or a phpBB consultant? I offer most phpBB services.

User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: Weird SQLServer errors when attaching files to a post

Post by Oyabun1 » Wed Sep 30, 2015 11:28 pm

MarkDHamill wrote:What code gets executed by the function cast_expr_to_bigint if the database driver has no such function?
If it isn't needed it isn't used. As you would know, phpBB uses a DBAL. The various data types are assigned by /phpbb/db/tools.php.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests

User avatar
MarkDHamill
Registered User
Posts: 3876
Joined: Fri Aug 02, 2002 12:36 am
Location: Florence, MA USA
Contact:

Re: Weird SQLServer errors when attaching files to a post

Post by MarkDHamill » Thu Oct 01, 2015 12:15 am

I was able to create a patch to the main phpBB code by changing line 191 of /phpbb/config/db.php as follows:

Code: Select all

		if ($this->db->sql_escape($key) == 'upload_dir_size')
		{
			$sql_update = $this->db->cast_expr_to_string('config_value + CAST (' . (int) $increment . ' AS bigint)');
		}
		else
		{
			$sql_update = $this->db->cast_expr_to_string($this->db->cast_expr_to_bigint('config_value') . ' + ' . (int) $increment);
		}
Of course this code may not work if the underlying database is not SQLServer.

This sure looks like a bug to me, so I'll put in a bug report.

Updated post to correct CAST statement when it didn't work with larger values. This seems to work.
Get the latest versions of my Digests and Smartfeed extensions.
Need phpBB services or a phpBB consultant? I offer most phpBB services.

Locked

Return to “[3.1.x] Support Forum”

cron