The 'text' type in the MySQL database is too small for me!

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
Verteron
Registered User
Posts: 228
Joined: Fri Apr 19, 2002 6:37 pm

The 'text' type in the MySQL database is too small for me!

Post by Verteron »

I run a fan fiction forum within my phpBB... people try and post exceedingly long stories and I think they're exceeding the limits of the text type in the database.

According to my research text's can hold up to 2^16 characters, which is 65,536. The stories are definitely longer than this. Can I replace it with a larger text type, without destroying the current posts table? If so, what SQL query should I run, and will any modifications be required to the code? I don't think so, there's no length validation AFAIK.

zoid
Registered User
Posts: 743
Joined: Fri Oct 12, 2001 6:29 am
Location: $SCRIPT_NAME
Contact:

Post by zoid »

You are right, According to http://www.mysql.com/doc/n/o/node_369.html TEXT is 2^16 (65536) and LONGTEXT is 2^32 (4294967296).

The particular query to modify the column type should be (can someone confirm that?)

Code: Select all

ALTER phpbb_posts_text CHANGE post_text post_text LONGTEXT
however I cannot tell you whether this creates compatibility issues with phpbb (probably only a developer can talk about that).

Alexander
Whatever you want to know, please do a Image Search before asking :).

Run your own Chatcommunity
>> PINO - Client/Server Chat for Windows <<

Verteron
Registered User
Posts: 228
Joined: Fri Apr 19, 2002 6:37 pm

Post by Verteron »

Thanks for your advice. The forum is relatively small and I back-up the db so I'll try it and if it all goes wrong I'll restore it and let you know. :D

Verteron
Registered User
Posts: 228
Joined: Fri Apr 19, 2002 6:37 pm

Post by Verteron »

Actually, the larger type is called LONGTEXT. I changed the field type in phpMyAdmin and it worked, perfectly. I'm still able to post and search, so I guess it must be working.... I hope, anyway.

Anyone who's had problems with this should read this thread... just out of interest, what's the developers' opinions on this? Should it make a difference?

Myridden
Registered User
Posts: 2
Joined: Sun Jun 02, 2002 8:52 am

Interesting, but what about MS SQL...

Post by Myridden »

Hi, :?

I am having the same trouble with a board I am hosting for a friend, the problem is I am using MS SQL Server 2000.

The docs indicate that the text data type is capable of holding 2 gig, but the length is defaulting to 16 (65535) and I haven't been able to change it.

When someone posts more than the 64k, thier post is cut off and when you return the rows in SQL it shows <Long Text> in the posts_text field.

Anyone know how to change the length of the text data type?

bennytheball
Registered User
Posts: 20
Joined: Wed Aug 13, 2003 11:09 pm

Re: Interesting, but what about MS SQL...

Post by bennytheball »

Myridden wrote: Hi, :?

I am having the same trouble with a board I am hosting for a friend, the problem is I am using MS SQL Server 2000.

The docs indicate that the text data type is capable of holding 2 gig, but the length is defaulting to 16 (65535) and I haven't been able to change it.

When someone posts more than the 64k, thier post is cut off and when you return the rows in SQL it shows <Long Text> in the posts_text field.

Anyone know how to change the length of the text data type?


I've been having similar problems with some messages on my board being truncated to what appears to be a maximum size of around 4000 characters (or roughly 700 words).

I've tried altering the size of the MSSQL database field that stores message texts, but have had no success unless I alter the data type of the field in question (eg from 'text' to 'varchar'), which prevents anything being posted at all.

Can any modification be made to the size of the database field where text is stored, or, failing that, to the data type of the field where text is stored, without wrecking the software?

Thanks,

Ben

Locked

Return to “2.0.x Support Forum”