[SQL] Increase Row Limit
Solved
F2000
Posted messages
37
Status
Membre
-
F2000 Posted messages 37 Status Membre -
F2000 Posted messages 37 Status Membre -
Hello,
My website (hosted by OVH) has a database.
For one of my tables, I have a TEXT type field that contains the text of my pages.
I am currently writing a substantial article (40,000 characters so far) and I just realized that when I save my article, it cuts off towards the end.
After some research, I read that there is a limit of 8060 bytes per row in a table, but that the TEXT field allows up to 2 GB of data (which is 1 billion characters according to the said site). The site also mentioned a ROW OVERFLOW option that allows bypassing this limit.
My question is simple: is there a way to increase the capacity allowed by my database for a table row?
Best regards,
My website (hosted by OVH) has a database.
For one of my tables, I have a TEXT type field that contains the text of my pages.
I am currently writing a substantial article (40,000 characters so far) and I just realized that when I save my article, it cuts off towards the end.
After some research, I read that there is a limit of 8060 bytes per row in a table, but that the TEXT field allows up to 2 GB of data (which is 1 billion characters according to the said site). The site also mentioned a ROW OVERFLOW option that allows bypassing this limit.
My question is simple: is there a way to increase the capacity allowed by my database for a table row?
Best regards,
Configuration: Windows Vista Firefox 3.0.5
2 réponses
8060 bytes per record, this concerns SQL Server... not necessarily other DBMS, MySQL in your case I think. Moreover, this does not concern TEXT type fields (among others).
In your case, you need to change the size of your field in the properties of your table in PHPMyAdmin.
--
Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
Internet connection: ADSL Orange 8M (Livebox)
In your case, you need to change the size of your field in the properties of your table in PHPMyAdmin.
--
Configuration: Linux Debian Lenny / Kernel 2.6.26 / Gnome / Iceweasel 3
Internet connection: ADSL Orange 8M (Livebox)
OK.
But how do I change the size? A TEXT field is not by default adaptable in size?
Do I need to specify a specific value? Because currently it's refusing a value for the TEXT field (or at least it's not taking it into account).
Ps: And indeed I'm using MySQL and PhpMyAdmin
Edit: After further research, MySQL is blocking my query at 65535 characters, which corresponds to the size limit for the TEXT field.
Now I read in the MySQL documentation (http://dev.mysql.com/doc/refman/5.0/fr/blob.html):
“The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you can send to the client program is determined by the amount of memory available on the server and the sizes of the communication buffers. You can change the size of the communication buffers, but you must do this on the server and the client at the same time. See Section 7.5.2, "Tuning Server Parameters".
For example, both mysql and mysqldump allow you to modify the client value of max_allowed_packet.”
But I’m still surprised that it's a buffer problem...
What do you think?
Edit2: Well, I finally changed my TEXT type to LONGTEXT and it works. The optimization program even advises me to use MEDIUMTEXT :p
In short, at least I've learned that the "TEXT" type is not infinite and that there's something bigger.
Thanks ;)
But how do I change the size? A TEXT field is not by default adaptable in size?
Do I need to specify a specific value? Because currently it's refusing a value for the TEXT field (or at least it's not taking it into account).
Ps: And indeed I'm using MySQL and PhpMyAdmin
Edit: After further research, MySQL is blocking my query at 65535 characters, which corresponds to the size limit for the TEXT field.
Now I read in the MySQL documentation (http://dev.mysql.com/doc/refman/5.0/fr/blob.html):
“The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you can send to the client program is determined by the amount of memory available on the server and the sizes of the communication buffers. You can change the size of the communication buffers, but you must do this on the server and the client at the same time. See Section 7.5.2, "Tuning Server Parameters".
For example, both mysql and mysqldump allow you to modify the client value of max_allowed_packet.”
But I’m still surprised that it's a buffer problem...
What do you think?
Edit2: Well, I finally changed my TEXT type to LONGTEXT and it works. The optimization program even advises me to use MEDIUMTEXT :p
In short, at least I've learned that the "TEXT" type is not infinite and that there's something bigger.
Thanks ;)